Hello, I'm architecture Jun, an architect who can write code and recite poetry. Today, let's talk about MySQL's current date and time function. I hope it can help you make progress!!!

MySQL get current date time function

Get current date + time function: now()

Get the current date time function "alt =" copy code ">

mysql> select now();

+---------------------+
| now() |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

Get the current date time function "alt =" copy code ">

Get current date + time (date + time) function: sysdate() The sysdate() date time function is similar to now(). The difference is that now() gets the value at the beginning of execution, and sysdate() gets the value dynamically during function execution. Just look at the following example:

Get the current date time function "alt =" copy code ">

This code is by Java Architect must see network-Architecture sorting
mysql> select now(), sleep(3), now();

+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
+---------------------+----------+---------------------+

Get the current date time function "alt =" copy code ">

The sysdate() date time function is rarely used in general.

MySQL get current timestamp function: current_timestamp, current_timestamp()

Get the current date time function "alt =" copy code ">

mysql> select current_timestamp, current_timestamp();

+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---------------------+---------------------+

Get the current date time function "alt =" copy code ">

MySQL date conversion function and time conversion function

MySQL Date/Time to Str function: date_format(date,format), time_format(time,format)

Get the current date time function "alt =" copy code ">

This code is by Java Architect must see network-Architecture sorting
mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301 |
+----------------------------------------------------+

Get the current date time function "alt =" copy code ">

MySQL date and time conversion function: date_format(date,format), time_format(time,format) can convert a date / time into a variety of string formats. It's str_ to_ An inverse transformation of the date (STR, format) function.

MySQL Str to Date function: str_to_date(str, format)

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

As you can see, str_to_date(str,format) conversion function can convert some messy strings into date format. In addition, it can also be converted to time. For "format", please refer to the MySQL manual.

MySQL (date, day) conversion function: to_days(date), from_days(days)

select to_days('0000-00-00'); -- 0
select to_days('2008-08-08'); -- 733627

MySQL (time, second) conversion function: time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'

MySQL patching date and time functions: makdate (year, dayofyear), maketime (hour, minute, second)

select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'

MySQL (Unix timestamp, date) conversion function

unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

Here is an example:

Get the current date time function "alt =" copy code ">

select unix_timestamp(); -- 1218290027
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800

select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
select from_unixtime(1218169800); -- '2008-08-08 12:30:00'

select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

Get the current date time function "alt =" copy code ">

MySQL date time calculation function

MySQL adds a time interval to the date: date_add()

Get the current date time function "alt =" copy code ">

set @dt = now();

select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day

Get the current date time function "alt =" copy code ">

MySQL adddate(), addtime() function, you can use date_add() instead. Here is date_ Example of addtime() function implemented by add():

Get the current date time function "alt =" copy code ">

mysql> set @dt = '2008-08-09 12:12:33';

mysql>
mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |
+-------------------------------------------------+

Get the current date time function "alt =" copy code ">

MySQL subtracts a time interval from the date: date_sub()

Get the current date time function "alt =" copy code ">

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+
| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 1997-12-30 22:58:59 |
+----------------------------------------------------------------+

Get the current date time function "alt =" copy code ">

MySQL date_sub() datetime function and date_add() has the same usage and will not be repeated.

MySQL date and time subtraction functions: datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2): Subtract two dates date1 - date2，Returns the number of days.
select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7

MySQL timediff(time1,time2): subtract time1 - time2 from two dates and return the time difference.

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

Note: the two parameter types of the timediff(time1,time2) function must be the same.

MySQL timestamp conversion, increment and decrement functions:

timestamp(date) -- date to timestamp
timestamp(dt,time) -- dt + time
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --

See the example section:

Get the current date time function "alt =" copy code ">

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00

MySQL timestampadd() Functions are similar to date_add(). 
select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

Get the current date time function "alt =" copy code ">

MySQL timestampdiff() function is much more powerful than datediff(). Datediff() can only calculate the number of days between two dates.

MySQL time zone conversion function

convert_tz(dt,from_tz,to_tz)

select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00

Time zone conversion can also be done through date_add, date_sub, timestampadd.

select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00