Of Stack

MySQL Get Current Date Time Function

  • 2021-08-31 09:30:10
  • OfStack

Get the current date + time (date + time) Function: now () 


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

Get the current date + time (date + time) Function: sysdate ()

The sysdate () date-time function is similar to now () except that now () gets the value at the start of execution and sysdate () gets the value dynamically when the function executes. Look at the following example to understand: 


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

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

MySQL gets 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, time conversion function

MySQL Date/Time to Str (date/time converted 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-time conversion function: date_format (date, format), time_format (time, format) can convert a date/time into various string formats. It is an inverse transform of the function str_to_date (str, format).

MySQL Str to Date (String 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, the str_to_date (str, format) conversion function converts a number of disorganized strings into date format. In addition, it can also be converted into time. "format" can be found in the MySQL manual.

MySQL (date, days) conversion functions: to_days (date), from_days (days) 


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

MySQL (time, second) conversion functions: 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 pieced together 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)

Here's an example: 


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'

MySQL Date and Time Calculation Function

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


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

The MySQL adddate (), addtime () functions can be replaced by date_add (). The following is an example of date_add () implementing the addtime () functionality: 


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

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


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

The date-time function MySQL date_sub () and the usage of date_add () are not repeated here.

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

MySQL datediff (date1, date2): Subtract date1-date2 from two dates to return days. 


select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7
MySQL timediff(time1,time2) : Subtract two dates  time1 - time2 , return  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 (timestamp) conversion, increase, decrease function: 


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

See the sample section: 


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

The MySQL timestampdiff () function is much more powerful than datediff (), which can only count the number of days between two dates (date).

MySQL Time Zone (timezone) Conversion Function

convert_tz(dt,from_tz,to_tz) 


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

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


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

Above is this site to introduce the MySQL to get the current date and time function, I hope to help you, if you have any questions welcome to leave me a message, this site will reply to you in time!

Related articles: