mysql date and time-Mysql Tutorial-php.cn

mysql date and time

韦小宝

Release： 2017-12-04 09:36:51

Original

1948 people have browsed it

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();

Copy after login

The above 5 functions can all get the current local time, but SYSDATE is different.

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

Copy after login

Execution result:

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

Copy after login

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();

Copy after login

Execution result:

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

Copy after login

1.2. Get the current local time Date

SELECT 
    CURDATE(), 
    CURRENT_DATE();

Copy after login

1.3. Get the current local time

SELECT 
    CURTIME(), 
    CURRENT_TIME();

Copy after login

1.4. Get the current UTC date and time

SELECT 
    UTC_TIMESTAMP();

Copy after login

1.5. Get the current UTC date

SELECT 
    UTC_DATE();

Copy after login

1.6. Get the current UTC time

SELECT 
    UTC_TIME();

Copy after login

1.7. Get Timestamp (Seconds elapsed since 1970)

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

Copy after login

2. Time operation

2.1. Extract time and date from string

SET @dt = &#39;2008-09-10 07:15:30.123456&#39;;
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

Copy after login

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

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

Copy after login

2.3. Get the last day of the specified date

SELECT LAST_DAY(&#39;2017-02-05&#39;);   -- 2017-02-28

Copy after login

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天

Copy after login

2.5. Adding and subtracting two dates and times

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

Copy after login

SELECT TIMEDIFF(&#39;2008-08-08 08:08:08&#39;, &#39;2008-08-08 00:00:00&#39;);  -- 08:08:08
SELECT TIMEDIFF(&#39;00:00:00&#39;, &#39;08:08:08&#39;);                        -- -08:08:08

Copy after login

2.6. Time formatting

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

Copy after login

2.7. Second calculation

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

Copy after login

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!