MySQL obtains the current date and time function example detailed explanation

MySQL obtains the current date and time function example detailed explanation

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

More references: https://www.jb51.net/article/201868.htm

This is the end of this article about MySQL function to get the current date and time. For more information about MySQL function to get the current date and time, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL DATE_ADD and ADDDATE functions add a specified time interval to a date
  • How to use MySQL DATEDIFF function to get the time interval between two dates
  • An article to deal with Mysql date and time functions
  • Summary of MySQL date and time functions (MySQL 5.X)
  • Summary of the use of MySQL date and time functions
  • mysql implements adding time automatically adding and updating time automatically updating operation

<<:  Docker container time zone adjustment operation

>>:  XHTML Getting Started Tutorial: Form Tags

Recommend

A brief talk about calculated properties and property listening in Vue

Table of contents 1. Computed properties Syntax: ...

Linux configuration without password login stand-alone and full distribution detailed tutorial

Table of contents 1: Single machine password-free...

Web2.0: Causes and Solutions of Information Overload

<br />Information duplication, information o...

How to use mysqldump for full and point-in-time backups

Mysqldump is used for logical backup in MySQL. Al...

Detailed explanation of angular content projection

Table of contents Single content projection Multi...

Introduction to container data volumes in Docker

Table of contents Docker container data volume Us...

Vue.js style layout Flutter business development common skills

Correspondence between flutter and css in shadow ...

A simple ID generation strategy: Implementation of generating globally unique ID from MySQL table

There are many ways to generate a global ID. Here...

Detailed explanation of Vue lazyload picture lazy loading example

Documentation: https://github.com/hilongjw/vue-la...

Detailed explanation of the basic use of react-navigation6.x routing library

Table of contents react-native project initializa...

JS ES new feature of variable decoupling assignment

Table of contents 1. Decoupled assignment of arra...

Example code for implementing dynamic column filtering in vue+element table

Requirement: When displaying data in a list, ther...

Vue-Router installation process and principle detailed

Table of contents 1. Front-end routing implementa...

Detailed explanation on how to install MySQL database on Alibaba Cloud Server

Preface Since I needed to install Zookeeper durin...

Reasons why MySQL 8.0 statistics are inaccurate

Preface Whether it is Oracle or MySQL, the new fe...