MySQL obtains the current date and time function.

MySQL obtains the current date and time function.

Obtain the current date + time (date + time) function: now ()

mysql> select now();+---------------------+| now() |+---------------------+| 2008-08-08 22:20:46 |+---------------------+

Function for obtaining the current date + time (date + time): sysdate ()

The sysdate () Date and time function is similar to now (). The difference is that now () is obtained at the execution start time, And sysdate () dynamically obtains the value during function execution. You can see the following example:

mysql> select now(), sleep(3), now();+---------------------+----------+---------------------+| now() | sleep(3) | now() |+---------------------+----------+---------------------+| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |+---------------------+----------+---------------------+

Sysdate () Date and time functions are rarely used.

MySQL obtains the current timestamp function: current_timestamp, current_timestamp ()

mysql> select current_timestamp, current_timestamp();+---------------------+---------------------+| current_timestamp | current_timestamp() |+---------------------+---------------------+| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |+---------------------+---------------------+

MySQL date conversion function and time Conversion Function

MySQL Date/Time to Str (convert date/time to string) functions: date_format (Date, format), time_format (Time, format)

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 |+----------------------------------------------------+

MySQL date and time conversion functions: date_format (date, format), time_format (time, format) can convert a date/time to a variety of string formats. It is an inverse conversion of the str_to_date (str, format) function.

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

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

We can see that the str_to_date (str, format) Conversion Function can convert disordered strings into date formats. In addition, it can be converted to time. For "format", see the MySQL manual.

MySQL (date, number of days) Conversion Function: to_days (date), from_days (days)

select to_days('0000-00-00'); -- 0select 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'); -- 3605select sec_to_time(3605); -- '01:00:05'

MySQL patchwork Date and Time Functions: makdedate (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)

The following is an example:

select unix_timestamp(); -- 1218290027select unix_timestamp('2008-08-08'); -- 1218124800select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800select 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'

MySQL Date and Time Calculation Functions

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

set @dt = now();select date_add(@dt, interval 1 day); -- add 1 dayselect date_add(@dt, interval 1 hour); -- add 1 hourselect 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

MySQL adddate () and addtime () functions can be replaced by date_add. The following is an example of addtime () implemented by date_add:

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 |+-------------------------------------------------+ 

MySQL is the date minus a time interval: date_sub ()

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 |+----------------------------------------------------------------+

The usage of MySQL date_sub () datetime function is the same as that of date_add.

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

MySQL datediff (date1, date2): two dates subtract date1-date2 and return the number of days.

Select datediff ('2017-08-08 ', '2017-08-01'); -- 7 select datediff ('2017-08-01 ', '2017-08-08 '); ---7 MySQL timediff (time1, time2): two dates minus time1-time2, return the time difference. Select timediff ('2017-08-08 08:08:08 ', '2017-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, addition, and subtraction functions:

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

See the example section:

Select timestamp ('2017-08-08 '); -- 2008-08-08 00: 00: 00 select timestamp ('2017-08-08 08:00:00', '01: 01: 01 '); -- 2008-08-08 09: 01: 01 select timestamp ('2017-08-08 08:00:00 ', '10 01:01:01'); -- 2008-08-18 09: 01: 01 select timestampadd (day, 1, '2017-08-08 08:00:00 '); -- 2008-08-09 08: 00: 00 select date_add ('2017-08-08 08:00:00', interval 1 day); -- 2008-08-09 08: 00: 00 MySQL timestampadd () function is similar to date_add (). Select timestampdiff (year, '2017-05-01 ', '2017-01-01'); ---1 select timestampdiff (day, '2017-05-01 ', '2017-01-01 '); -- 485 select timestampdiff (hour, '2017-08-08 12:00:00 ', '2017-08-08 00:00:00 '); -- 12 select datediff ('2017-08-08 12:00:00 ', '2017-08-01 00:00:00'); -- 7

MySQL timestampdiff () functions are much more powerful than datediff () functions. datediff () can only calculate the number of days for the difference 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

You can also use date_add, date_sub, and timestampadd to convert time zones.

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

The above section describes how to obtain the current date and time functions of MySQL. I hope it will be helpful to you. If you have any questions, please leave a message. The editor will reply to you in time!