Summary of the use of MySQL date and time functions

Summary of the use of MySQL date and time functions

This article is based on MySQL 8.0

This article introduces MySQL functions for date and time operations.

Date and Time Functions

function describe
ADDDATE() Adding time values ​​to date values
ADDTIME() Add time
CONVERT_TZ() Convert from one time zone to another
CURDATE() Returns the current date
CURRENT_DATE(), CURRENT_DATE Synonymous with CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonymous with CURDATE()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonymous with NOW()
CURTIME() Returns the current time
DATE() Gets the date portion of a date or datetime expression
DATE_ADD() Add a time value (interval) to a date value
DATE_FORMAT() Formats the date in the specified format
DATE_SUB() Subtract a time value (interval) from a date
DATEDIFF() Subtract two dates
DAY() Synonymous with DAYOFMONTH()
DAYNAME() Returns the weekday name
DAYOFMONTH() Returns the day of the month (0-31)
DAYOFWEEK() Returns the weekdays sorted by the parameter
DAYOFYEAR() Returns the day of the year (1-366)
EXTRACT() Get part of a date
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format Unix timestamp as date
GET_FORMAT() Returns the date format string
HOUR() Get hours
LAST_DAY Returns the last day of the argument month
LOCALTIME(), LOCALTIME Synonymous with NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP() Synonymous with NOW()
MAKEDATE() Create a date from year and day
MAKETIME() Create a time from hours, minutes and seconds
MICROSECOND() Returns the microseconds from the argument
MINUTE() Return parameter minutes
MONTH() Returns the month from a past date
MONTHNAME() Returns the name of the month
NOW() Returns the current date and time
PERIOD_ADD() Add a period to year-month
PERIOD_DIFF() Returns the number of months between a period
QUARTER() Returns the quarter from a date argument
SEC_TO_TIME() Convert seconds to "hh:mm:ss" format
SECOND() Returns seconds (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() Synonymous with DATE_SUB() when called with three arguments.
SUBTIME() Subtract time
SYSDATE() Returns the time the function was executed
TIME() Get the time according to the passed expression
TIME_FORMAT() Format is time
TIME_TO_SEC() Returns the argument converted to seconds
TIMEDIFF() Time Subtraction
TIMESTAMP() For a single argument, this function returns a date or datetime expression; for two arguments, it returns the sum of the arguments.
TIMESTAMPADD() Adding intervals to datetime expressions
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_DAYS() Returns the date parameter converted to day
TO_SECONDS() Returns the date or datetime argument converted to seconds since year 0
UNIX_TIMESTAMP() Returns the Unix timestamp
UTC_DATE() Returns the current UTC date
UTC_TIME() Returns the current UTC time
UTC_TIMESTAMP() Returns the current UTC date and time
WEEK() Returns the day of the week
WEEKDAY() Returns the working day number
WEEKOFYEAR() Returns the calendar week of a date (1-53)
YEAR() Return Year
YEARWEEK() Returns the year and day of the week

Let's take a look at examples of commonly used MySQL date and time functions.

Get the current date and time

CURDATE()

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 conversion

The 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:

  • DATE_FORMAT(date,format): Formats a date value according to a format string.

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().

Format describe
%a Abbreviated day of the week (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English prefix (0th, 1st, 2nd, 3rd, …)
%d A day in a month, value (00..31)
%e A day in a month, value (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hours (01..12)
%I Hours (01..12)
%i Minute, value (00..59)
%j Day of the year (001..366)
%k Hours (0..23)
%l Hours (1..12)
%M Month name (January..December)
%m Month, value (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%W Day of the week (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%Y year, numeric, 4 digits
%y year, numeric, 2 digits

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

  • DATE_ADD(date,INTERVAL expr unit),
  • DATE_SUB(date,INTERVAL expr unit)

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(expr1,expr2)

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:
  • An article to deal with Mysql date and time functions
  • Summary of MySQL date and time functions (MySQL 5.X)
  • PHP Mysql date and time function collection
  • mYsql date and time functions do not require help
  • MySQL date and time function knowledge summary

<<:  Let's talk about bitwise operations in React source code in detail

>>:  Solve the problem that docker run or docker restart will automatically exit when starting the image

Recommend

Analysis of product status in interactive design that cannot be ignored in design

In the process of product design, designers always...

This article will show you the basics of JavaScript: deep copy and shallow copy

Table of contents Shallow copy Deep Copy Replenis...

Docker installs ClickHouse and initializes data testing

Clickhouse Introduction ClickHouse is a column-or...

Introduction to ApplicationHost.config (IIS storage configuration area file)

For a newly created website, take ASP.NET MVC5 as...

Analysis of the method of setting up scheduled tasks in mysql

This article uses an example to describe how to s...

W3C Tutorial (10): W3C XQuery Activities

XQuery is a language for extracting data from XML...

Summary of 10 advanced tips for Vue Router

Preface Vue Router is the official routing manage...

SQL query for users who have placed orders for at least seven consecutive days

Create a table create table order(id varchar(10),...

How to implement remote automatic backup of MongoDB in Linux

Preface After reading the previous article about ...

Vue3 (III) Website Homepage Layout Development

Table of contents 1. Introduction 2. Actual Cases...

Detailed explanation of Vue slot

1. Function : Allows the parent component to inse...

mysql installer web community 5.7.21.0.msi installation graphic tutorial

This article example shares the specific code for...

Solve the problem of MySql8.0 checking transaction isolation level error

Table of contents MySql8.0 View transaction isola...

Use Grafana+Prometheus to monitor MySQL service performance

Prometheus (also called Prometheus) official webs...

Share 8 very useful CSS development tools

CSS3 Patterns Gallery This CSS3 pattern library s...