mysql date and time

There will definitely be many students who don’t understand the mysql acquisition time at the beginning. Today we will talk about the time and date of mysql in detail. For mysqlStudents who don’t understand time and date can read more~

1. Obtain date and time

1,1 Get the current local date and time

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

The above 5 functions can all 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 it slept for 3 seconds, the time obtained twice before and after NOW() is the same, which means that the time obtained by the NOW() function is during the entire The time when the SQL statement starts executing, no matter how many NOW() functions there are in the SQL statement, the time obtained is the same.

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

But SYSDATE is different. It obtains the real-time time when the SYSDATE() function is executed:

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 time 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 Timestamp (Seconds elapsed since 1970)

SELECT UNIX_TIMESTAMP();  -- 当前时间时间戳
SELECT UNIX_TIMESTAMP('2017-11-09 12:30:00');  -- 自1970年到2017-11-09 12:30:00经过的秒数

2. Time operation

2.1. Extract time and date from string

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

2.2. Get the position of a certain day in a week, month, and year

SET @dt = '2017-11-09';
SELECT DAYOFWEEK(@dt);   -- 5       星期日为0，5代表星期四
SELECT DAYOFMONTH(@dt);  -- 9       一个月的第9天
SELECT DAYOFYEAR(@dt);   -- 313     2017年的第313天

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:10:20.0000001";
SELECT DATE_ADD(@dt, INTERVAL 1 DAY);        -- 加1天
SELECT DATE_ADD(@dt, INTERVAL 2 HOUR);       -- 加2小时
SELECT DATE_ADD(@dt, INTERVAL 1 MINUTE);     -- 加1分钟
SELECT DATE_ADD(@dt, INTERVAL 1 SECOND);
SELECT DATE_ADD(@dt, INTERVAL 1 MICROSECOND); -- 加1微妙
SELECT DATE_ADD(@dt, INTERVAL 1 WEEK);        -- 加1周
SELECT DATE_ADD(@dt, INTERVAL 1 MONTH);
SELECT DATE_ADD(@dt, INTERVAL 1 QUARTER);     -- 加1个季度
SELECT DATE_ADD(@dt, INTERVAL 1 YEAR);
SELECT DATE_ADD(@dt, INTERVAL -1 DAY);       -- 减1天

2.5. Adding and subtracting two dates and times

SELECT DATEDIFF('2008-08-08', '2008-08-01');  -- 7
SELECT DATEDIFF('2008-08-01', '2008-08-08');  -- -7  第一个参数减去第二个参数

SELECT TIMEDIFF('2008-08-08 08:08:08', '2008-08-08 00:00:00');  -- 08:08:08
SELECT TIMEDIFF('00:00:00', '08:08:08');                        -- -08:08:08

2.6. Time formatting

SELECT DATE_FORMAT('2008-08-08 22:23:00', '%W %M %Y');      -- Friday August 2008
SELECT DATE_FORMAT('2008-08-08 22:23:01', '%Y%m%d%H%i%s');  -- 20080808222301
SELECT TIME_FORMAT('22:23:01', '%H.%i.%s');                 -- 22.23.01

2.7. Second calculation

计算指定时间折合多少秒，如00:01:00表示1分钟，等于60秒。
SELECT TIME_TO_SEC('01:00:05');  -- 3605
SELECT SEC_TO_TIME(3605);        -- '01:00:05'

The above is all the content of mysql date and time. I hope it can bring new understanding and inspiration to the students

Related recommendations:

MySQL time basic tutorial

MySQL time and date query methods and functions

mysql time PHP code for function using mysql database difference comparison

The above is the detailed content of mysql date and time. For more information, please follow other related articles on the PHP Chinese website!