Shulou(Shulou.com)06/01 Report--

The following mainly brings you the operation method of obtaining mysql date and time. I hope these contents can bring you practical use. This is also the main purpose of this article that I edit the operation method of getting mysql date and time. All right, don't talk too much nonsense, let's just read the following.

I. date and time acquisition

1 get the date and time of the current local

SELECT NOW (), CURRENT_TIMESTAMP (), LOCALTIME (), LOCALTIMESTAMP (), SYSDATE ()

Each of the above five functions can get the current local time, but SYSDATE is different.

SELECT NOW (), SLEEP (3), NOW ()

Execution result:

NOW () sleep (3) NOW () 2017-11-09 17:21:09 0 2017-11-09 17:21:09

Although sleep takes 3 seconds, the time obtained twice before and after NOW () is the same, that is, the time obtained by the NOW () function is the same when the entire SQL statement starts to execute, no matter how many NOW () functions are in the SQL statement.

The CURRENT_TIMESTAMP (), LOCALTIME (), LOCALTIMESTAMP (), and NOW () functions are the same.

Unlike SYSDATE, however, it gets the real-time time when the SYSDATE () function executes:

SELECT SYSDATE (), SLEEP (3), SYSDATE ()

Execution result:

SYSDATE () sleep (3) SYSDATE () 2017-11-09 17:25:05 0 2017-11-09 17:25:08

1.2. Get the current local date

SELECT CURDATE (), CURRENT_DATE ()

1.3. Get the current local time

SELECT CURTIME (), CURRENT_TIME ()

1.4.Get the current UTC date and time

SELECT UTC_TIMESTAMP ()

1.5. get the current UTC date

SELECT UTC_DATE ()

1.6. get the current UTC time

SELECT UTC_TIME ()

1.7. Get the timestamp (seconds since 1970)

SELECT UNIX_TIMESTAMP ();-- current timestamp SELECT UNIX_TIMESTAMP ('2017-11-09 12 30-0');-- seconds elapsed from 1970 to 2017-11-09 12:30:00

Second, time operation

2.1. Extract time and date from string

SET @ dt = '2008-09-1007 dt);-- 2008-09-10SELECT TIME (@ dt);-- 07:15:30.123456SELECT YEAR (@ dt);-- 2008SELECT QUARTER (@ dt);-- 3SELECT MONTH (@ dt);-- 9SELECT WEEK (@ dt);-- 36SELECT DAY (@ dt);-- 10SELECT HOUR (@ dt) -7SELECT MINUTE (@ dt);-15SELECT SECOND (@ dt);-30SELECT MICROSECOND (@ dt);-123456

2.2. Get where a day is in a week, a month, or a year

SET @ dt = '2017-11-09 SELECT DAYOFYEAR select DAYOFWEEK (@ dt);-- 5 Sunday is 0 for SELECT DAYOFMONTH (@ dt);-- 9 is the ninth day of the month SELECT DAYOFYEAR (@ dt);-313 the 313th day of 2017

2.3. Get the last day of the specified date

SELECT LAST_DAY ('2017-02-05');-2017-02-28

2.4. Time addition and subtraction

SET @ dt = "2017-11-09 17 SELECT DATE_ADD 10 SELECT DATE_ADD 20.0000001"; SELECT DATE_ADD (@ dt, INTERVAL 1 DAY);-- plus 1 day SELECT DATE_ADD (@ dt, INTERVAL 2 HOUR);-- plus 2 hours SELECT DATE_ADD (@ dt, INTERVAL 1 MINUTE);-- plus 1 minute SELECT DATE_ADD (@ dt, INTERVAL 1 SECOND); SELECT DATE_ADD (@ dt, INTERVAL 1 MICROSECOND) -- add 1 subtle SELECT DATE_ADD (@ dt, INTERVAL 1 WEEK);-- add 1 week SELECT DATE_ADD (@ dt, INTERVAL 1 MONTH); SELECT DATE_ADD (@ dt, INTERVAL 1 QUARTER);-- add 1 quarterly SELECT DATE_ADD (@ dt, INTERVAL 1 YEAR); SELECT DATE_ADD (@ dt, INTERVAL-1 DAY);-- minus 1 day

2.5. Two dates and times plus and minus

SELECT DATEDIFF ('2008-08-08-08,' 2008-08-01');-- 7SELECT DATEDIFF ('2008-08-01,' 2008-08-08');-- 7 the first parameter minus the second parameter SELECT TIMEDIFF ('2008-08-08 08,' 2008-08-08 00);-- 08:08:08SELECT TIMEDIFF -08:08:08

2.6. Time formatting

SELECT DATE_FORMAT ('2008-08-0822: 23 SELECT DATE_FORMAT,'% W% M% Y');-- Friday August 2008SELECT DATE_FORMAT ('2008-08-0822: 23 20080808222301SELECT TIME_FORMAT,'% Y% m% d% H% I% s');-- 22. 23.01

2.7. Second calculation

Calculates how many seconds the specified time is equivalent to, such as 00:01:00 means 1 minute, equal to 60 seconds. SELECT TIME_TO_SEC ('01VOLGOUR 05');-- 3605SELECT SEC_TO_TIME (3605);--' 01VOLARO 05'

For the above about the operation method of getting the date and time of mysql, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.