MySQL gets the current date and time function

MySQL gets the current date and time function

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 and time function is similar to now(), except that now() gets its value at the beginning of execution, while sysdate() gets its value dynamically when the function is executed. Take a 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 |
+---------------------+----------+---------------------+

sysdate() Date and time function, generally rarely used.

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) function: 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 into various string formats. It is an inverse transformation of the str_to_date(str,format) function.

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 can convert some messy strings into date format. Alternatively, it can be converted to time. For "format", please refer to the MySQL manual.

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

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

MySQL (time, seconds) 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 makedate(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 functions

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

Here is 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 functions

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

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

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

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 subtracts an interval from a date: 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 MySQL date_sub() date and time function has the same usage as date_add(), so I will not go into details here.

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

MySQL datediff(date1,date2): Subtract date1 - date2 from two dates and return 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 parameters of the timediff(time1, time2) function must be of the same type.

MySQL timestamp conversion, increase, and decrease functions:

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

See the examples section:

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() function is 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

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

MySQL time zone conversion functions

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

The above is the MySQL function for getting the current date and time that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time!

You may also be interested in:
  • Common date comparison and calculation functions in MySQL
  • MySQL date functions and date conversion and formatting functions
  • Detailed explanation of commonly used date and time/numeric functions in MySQL (must read)
  • Detailed explanation of single-row function code of date type in MySQL

<<:  Detailed explanation of how to implement login function by combining React with Antd's Form component

>>:  Detailed explanation of how to exit Docker container without closing it

Recommend

MySQL quickly inserts 100 million test data

Table of contents 1. Create a table 1.1 Create te...

Implementation code for installing vsftpd in Ubuntu 18.04

Install vsftpd $ sudo apt-get install vsftpd -y S...

Linux kernel device driver virtual file system notes

/******************** * Virtual File System VFS *...

Analysis of uniapp entry-level nvue climbing pit record

Table of contents Preface Hello World image Set b...

mysql-8.0.15-winx64 uses the zip package to install and the service is shut down immediately after starting

The MySQL version used in this example is mysql-8...

Docker image creation, uploading, pulling and deployment operations (using Alibaba Cloud)

Since I found that the push image always timed ou...

Detailed explanation of formatting numbers in MySQL

Recently, due to work needs, I need to format num...

mysql8.0.11 winx64 installation and configuration tutorial

The installation tutorial of mysql 8.0.11 winx64 ...

Vue implements a search box with a magnifying glass

This article shares with you how to use Vue to im...

Detailed explanation of the difference between MySQL normal index and unique index

Table of contents 1 Conceptual distinction 2 Case...

Detailed explanation of the 8 attribute values ​​of the background attribute (interview question)

The value of the background property in CSS backg...

Why node.js is not suitable for large projects

Table of contents Preface 1. Application componen...

Use CSS to achieve three-column adaptive layout (fixed width on both sides, adaptive in the middle)

The so-called three-column adaptive layout means ...

CSS3 realizes the red envelope shaking effect

There is a requirement to realize the shaking eff...

Share the 15 best HTML/CSS design and development frameworks

Professional web design is complex and time-consu...