MySQL SYSDATE() Function
SELECT SYSDATE();
The SYSDATE() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH:MM:SS" (string) or as YYYYMMDDHHMMSS (numeric)
-------------
SELECT TIME("2021-06-06 19:30:10");
--------
The TIME_FORMAT() function formats a time by a specified format.
TIME_FORMAT(time, format)
Format Description
%f Microseconds (000000 to 999999)
%H Hour (00 to 23)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)
SELECT TIME_FORMAT("19:30:10", "%h %i %s %p");
SELECT TIME_FORMAT("19:30:10", "%r");
SELECT TIME_FORMAT("19:30:10", "%T");
------------------
The TIME_TO_SEC() function converts a time value into seconds.
SELECT TIME_TO_SEC("03:30:00.999999");
SELECT TIME_TO_SEC("-03:30:00");
-----------
The TIMEDIFF() function returns the difference between two time/datetime expressions.
Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.
SELECT TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10");
------------
The TIMESTAMP() function returns a datetime value based on a date or datetime value.
Note: If there are specified two arguments with this function, it first adds the second argument to the first, and then returns a datetime value.
SELECT TIMESTAMP("2017-07-23", "13:10:11");
SELECT TIMESTAMP("2017-07-23", "13:10:11");
-----------------
The TO_DAYS() function returns the number of days between a date and year 0 (date "0000-00-00").
The TO_DAYS() function can be used only with dates within the Gregorian calendar.
SELECT TO_DAYS("0001-06-06 09:34:00");
-------------
The WEEK() function returns the week number for a given date (a number from 0 to 53).
--------
The WEEKDAY() function returns the weekday number for a given date.
Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday.
SELECT WEEKDAY(CURDATE());
---------------
The WEEKOFYEAR() function returns the week number for a given date (a number from 1 to 53).
Note: This function assumes that the first day of the week is Monday and the first week of the year has more than 3 days.
------------
The YEAR() function returns the year part for a given date (a number from 1000 to 9999).
---------
The YEARWEEK() function returns the year and week number (a number from 0 to 53) for a given date.
YEARWEEK(date, firstdayofweek)
date Required. The date or datetime value to extract the year and week number from
firstdayofweek
Optional. Specifies what day the week starts on. Can be one of the following:
0 - First day of week is Sunday
1 - First day of week is Monday and the first week has more than 3 days
2 - First day of week is Sunday
3 - First day of week is Monday and the first week has more than 3 days
4 - First day of week is Sunday and the first week has more than 3 days
5 - First day of week is Monday
6 - First day of week is Sunday and the first week has more than 3 days
7 - First day of week is Monday
-----------------
Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use vlrinst
Database changed
MariaDB [vlrinst]> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-06-08 21:19:21 |
+---------------------+
1 row in set (0.047 sec)
MariaDB [vlrinst]> select time();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
MariaDB [vlrinst]> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-06-08 21:20:32 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME("2021-06-06 19:30:10");
+-----------------------------+
| TIME("2021-06-06 19:30:10") |
+-----------------------------+
| 19:30:10 |
+-----------------------------+
1 row in set (0.016 sec)
MariaDB [vlrinst]> SELECT TIME(now());
+-------------+
| TIME(now()) |
+-------------+
| 21:21:00 |
+-------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME_FORMAT("19:30:10", "%h %i %s %p");
+----------------------------------------+
| TIME_FORMAT("19:30:10", "%h %i %s %p") |
+----------------------------------------+
| 07 30 10 PM |
+----------------------------------------+
1 row in set (0.020 sec)
MariaDB [vlrinst]> SELECT TIME_FORMAT(now(), "%h %i %s %p");
+-----------------------------------+
| TIME_FORMAT(now(), "%h %i %s %p") |
+-----------------------------------+
| 09 24 50 PM |
+-----------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT now(),TIME_FORMAT(now(), "%h %i %s %p");
+---------------------+-----------------------------------+
| now() | TIME_FORMAT(now(), "%h %i %s %p") |
+---------------------+-----------------------------------+
| 2021-06-08 21:25:09 | 09 25 09 PM |
+---------------------+-----------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT now(),TIME_FORMAT(now(), "%r");
+---------------------+--------------------------+
| now() | TIME_FORMAT(now(), "%r") |
+---------------------+--------------------------+
| 2021-06-08 21:26:16 | 09:26:16 PM |
+---------------------+--------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT now(),TIME_FORMAT(now(), "%T");
+---------------------+--------------------------+
| now() | TIME_FORMAT(now(), "%T") |
+---------------------+--------------------------+
| 2021-06-08 21:26:45 | 21:26:45 |
+---------------------+--------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME_TO_SEC("03:30:00.999999");
+--------------------------------+
| TIME_TO_SEC("03:30:00.999999") |
+--------------------------------+
| 12600.999999 |
+--------------------------------+
1 row in set (0.053 sec)
MariaDB [vlrinst]> SELECT TIME_TO_SEC("01:00:00");
+-------------------------+
| TIME_TO_SEC("01:00:00") |
+-------------------------+
| 3600 |
+-------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME_TO_SEC("24:00:00");
+-------------------------+
| TIME_TO_SEC("24:00:00") |
+-------------------------+
| 86400 |
+-------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME_TO_SEC("-24:00:00");
+--------------------------+
| TIME_TO_SEC("-24:00:00") |
+--------------------------+
| -86400 |
+--------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10") |
+--------------------------------------------------------+
| 00:05:01 |
+--------------------------------------------------------+
1 row in set (0.030 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("now(), "1982-06-06 13:10:10");
"> ;
"> Bye
'USE' is not recognized as an internal or external command,
operable program or batch file.
Mounika@MOUNIKA-PC c:\xampp
# use vlrinst;
'use' is not recognized as an internal or external command,
operable program or batch file.
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use vlrinst;
Database changed
MariaDB [vlrinst]> SELECT TIMEDIFF("now()", "1982-06-06 13:10:10");
+------------------------------------------+
| TIMEDIFF("now()", "1982-06-06 13:10:10") |
+------------------------------------------+
| NULL |
+------------------------------------------+
1 row in set, 1 warning (0.017 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2000-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2000-06-06 13:10:10") |
+--------------------------------------------------------+
| 838:59:59 |
+--------------------------------------------------------+
1 row in set, 1 warning (0.027 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("1982-06-06 13:15:11", "2000-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("1982-06-06 13:15:11", "2000-06-06 13:10:10") |
+--------------------------------------------------------+
| -838:59:59 |
+--------------------------------------------------------+
1 row in set, 1 warning (0.000 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2000-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2000-06-06 13:10:10") |
+--------------------------------------------------------+
| 838:59:59 |
+--------------------------------------------------------+
1 row in set, 1 warning (0.001 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10") |
+--------------------------------------------------------+
| 00:05:01 |
+--------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2021-05-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2021-05-06 13:10:10") |
+--------------------------------------------------------+
| 744:05:01 |
+--------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIMESTAMP("2017-07-23", "13:10:11");
+--------------------------------------+
| TIMESTAMP("2017-07-23", "13:10:11") |
+--------------------------------------+
| 2017-07-23 13:10:11 |
+--------------------------------------+
1 row in set (0.024 sec)
MariaDB [vlrinst]> SELECT TO_DAYS(now());
+----------------+
| TO_DAYS(now()) |
+----------------+
| 738314 |
+----------------+
1 row in set (0.010 sec)
MariaDB [vlrinst]> SELECT TO_DAYS(now());
+----------------+
| TO_DAYS(now()) |
+----------------+
| 738314 |
+----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TO_DAYS("0001-06-06 09:34:00");
+--------------------------------+
| TO_DAYS("0001-06-06 09:34:00") |
+--------------------------------+
| 522 |
+--------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TO_DAYS("0001-00-00 09:34:00");
+--------------------------------+
| TO_DAYS("0001-00-00 09:34:00") |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set, 1 warning (0.000 sec)
MariaDB [vlrinst]> SELECT TO_DAYS("0001-00-01 09:34:00");
+--------------------------------+
| TO_DAYS("0001-00-01 09:34:00") |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set, 1 warning (0.000 sec)
MariaDB [vlrinst]> SELECT TO_DAYS("0001-01-01 09:34:00");
+--------------------------------+
| TO_DAYS("0001-01-01 09:34:00") |
+--------------------------------+
| 366 |
+--------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT week(now());
+-------------+
| week(now()) |
+-------------+
| 23 |
+-------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT weekday(now());
+----------------+
| weekday(now()) |
+----------------+
| 1 |
+----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT weekofyear(now());
+-------------------+
| weekofyear(now()) |
+-------------------+
| 23 |
+-------------------+
1 row in set (0.007 sec)
MariaDB [vlrinst]> SELECT yearweek(now());
+-----------------+
| yearweek(now()) |
+-----------------+
| 202123 |
+-----------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT yearweek(now(),1);
+-------------------+
| yearweek(now(),1) |
+-------------------+
| 202123 |
+-------------------+
1 row in set (0.030 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.