This article introduces MySQL functions for date and time operations. Date and Time Functions
Let's take a look at examples of commonly used MySQL date and time functions. Get the current date and timeCURDATE() Returns the current date in "YYYY-MM-DD" or YYYYMMDD format, depending on whether the function is used in a string context or a numeric context. mysql> SELECT CURDATE(); -> '2008-06-13' mysql> SELECT CURDATE() + 0; -> 20080613 CURTIME([fsp]) Returns the current time in 'hh:mm:ss' or hhmmss format, depending on whether the function is used in a string context or a numeric context. The value is expressed in the session time zone. If the specified fsp precision is from 0 to 6 decimal places, then the decimal precision is from 0 to 6. mysql> SELECT CURTIME(); -> '23:50:26' mysql> SELECT CURTIME() + 0; -> 235026.000000 NOW([fsp]) Returns the current date and time in 'YYYY-MM-DD hh:MM:ss' or YYYYMMDDhhmmss format, depending on whether the function is used in a string context or a numeric context. The value is expressed in the session time zone. mysql> SELECT NOW(); -> '2007-12-15 23:50:26' mysql> SELECT NOW() + 0; -> 20071215235026.000000 Time and date format conversionThe same date and time can be expressed in many different ways, and sometimes it is necessary to convert between different formats. In MySQL, the date_format() function is used:
The relevant formats are as follows. In addition to being used in this function, the format can also be used in: STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP().
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', -> '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', -> '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52' mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00' Date and time operations
These functions perform date arithmetic to shift dates forward or backward. The date parameter specifies a starting date or datetime value. expr is an expression that specifies the interval value to be added or subtracted from the start date. expr is evaluated as a string; it may begin with - to indicate a negative interval. unit is a keyword indicating the units that the expression should use. mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY); -> '2018-05-02' mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR); -> '2017-05-01' mysql> SELECT DATE_ADD('2020-12-31 23:59:59', -> INTERVAL 1 SECOND); -> '2021-01-01 00:00:00' mysql> SELECT DATE_ADD('2018-12-31 23:59:59', -> INTERVAL 1 DAY); -> '2019-01-01 23:59:59' mysql> SELECT DATE_ADD('2100-12-31 23:59:59', -> INTERVAL '1:1' MINUTE_SECOND); -> '2101-01-01 00:01:00' mysql> SELECT DATE_SUB('2025-01-01 00:00:00', -> INTERVAL '1 1:1:1' DAY_SECOND); -> '2024-12-30 22:58:59' mysql> SELECT DATE_ADD('1900-01-01 00:00:00', -> INTERVAL '-1 10' DAY_HOUR); -> '1899-12-30 14:00:00' mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY); -> '1997-12-02' mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002', -> INTERVAL '1.999999' SECOND_MICROSECOND); -> '1993-01-01 00:00:01.000001'
DATEDIFF() returns expr1−expr2, expressed as the number of days from one date to another. expr1 and expr2 are date or date and time expressions. Only the date portion of the value is used in the calculation. mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); -> 1 mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31'); -> -31 refer to: 【1】:12.7 Date and Time Functions [2]: MySQL function to get the current date and time 【3】:A guide to MySQL date and time functions This is the end of this article about MySQL date and time functions. For more information about MySQL date and time functions, 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:
|
<<: Let's talk about bitwise operations in React source code in detail
In the process of product design, designers always...
Table of contents Shallow copy Deep Copy Replenis...
Clickhouse Introduction ClickHouse is a column-or...
For a newly created website, take ASP.NET MVC5 as...
This article uses an example to describe how to s...
XQuery is a language for extracting data from XML...
Preface Vue Router is the official routing manage...
Create a table create table order(id varchar(10),...
Preface After reading the previous article about ...
Table of contents 1. Introduction 2. Actual Cases...
1. Function : Allows the parent component to inse...
This article example shares the specific code for...
Table of contents MySql8.0 View transaction isola...
Prometheus (also called Prometheus) official webs...
CSS3 Patterns Gallery This CSS3 pattern library s...