Skip to content

Latest commit

 

History

History
executable file
·
450 lines (397 loc) · 8.99 KB

Date_and_time_functions.md

File metadata and controls

executable file
·
450 lines (397 loc) · 8.99 KB

Date and time functions

Note, that CURTIME(), UTC_TIME(), UTC_TIMESTAMP(), and TIMEDIFF() can be promoted to numeric types using arbitrary conversion functions such as BIGINT(), DOUBLE(), etc.

NOW()

Returns the current timestamp as an INTEGER.

select NOW();
+------------+
| NOW()      |
+------------+
| 1615788407 |
+------------+

CURTIME()

Returns the current time in the local timezone in hh:ii:ss format.

select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 07:06:30  |
+-----------+

CURDATE()

Returns the current date in the local timezone in YYYY-MM-DD format.

select curdate();
+------------+
| curdate()  |
+------------+
| 2023-08-02 |
+------------+

UTC_TIME()

Returns the current time in UTC timezone in hh:ii:ss format.

select UTC_TIME();
+------------+
| UTC_TIME() |
+------------+
| 06:06:18   |
+------------+

UTC_TIMESTAMP()

Returns the current time in UTC timezone in YYYY-MM-DD hh:ii:ss format.

select UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2021-03-15 06:06:03 |
+---------------------+

SECOND()

Returns the integer second (in 0..59 range) from a timestamp argument, according to the current timezone.

select second(now());
+---------------+
| second(now()) |
+---------------+
| 52            |
+---------------+

MINUTE()

Returns the integer minute (in 0..59 range) from a timestamp argument, according to the current timezone.

select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 5             |
+---------------+

HOUR()

Returns the integer hour (in 0..23 range) from a timestamp argument, according to the current timezone.

select hour(now());
+-------------+
| hour(now()) |
+-------------+
| 7           |
+-------------+

DAY()

Returns the integer day of the month (in 1..31 range) from a timestamp argument, according to the current timezone.

select day(now());
+------------+
| day(now()) |
+------------+
| 15         |
+------------+

MONTH()

Returns the integer month (in 1..12 range) from a timestamp argument, according to the current timezone.

select month(now());
+--------------+
| month(now()) |
+--------------+
| 3            |
+--------------+

QUARTER()

Returns the integer quarter of the year (in 1..4 range) from a timestamp argument, according to the current timezone.

select quarter(now());
+--------------+
| quarter(now()) |
+--------------+
| 2            |
+--------------+

YEAR()

Returns the integer year (in 1969..2038 range) from a timestamp argument, according to the current timezone.

select year(now());
+-------------+
| year(now()) |
+-------------+
| 2021        |
+-------------+

DAYNAME()

Returns the weekday name for a given timestamp argument, according to the current timezone.

select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Wednesday      |
+----------------+

MONTHNAME()

Returns the name of the month for a given timestamp argument, according to the current timezone.

select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| August           |
+------------------+

DAYOFWEEK()

Returns the integer weekday index (in 1..7 range) for a given timestamp argument, according to the current timezone. Note that the week starts on Sunday.

select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
| 5                |
+------------------+

DAYOFYEAR()

Returns the integer day of the year (in 1..366 range) for a given timestamp argument, according to the current timezone.

select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
|              214 |
+------------------+

YEARWEEK()

Returns the integer year and the day code of the first day of current week (in 1969001..2038366 range) for a given timestamp argument, according to the current timezone.

select yearweek(now());
+-----------------+
| yearweek(now()) |
+-----------------+
|         2023211 |
+-----------------+

YEARMONTH()

Returns the integer year and month code (in 196912..203801 range) from a timestamp argument, according to the current timezone.

select yearmonth(now());
+------------------+
| yearmonth(now()) |
+------------------+
| 202103           |
+------------------+

YEARMONTHDAY()

Returns the integer year, month, and date code (ranging from 19691231 to 20380119) based on the current timezone.

select yearmonthday(now());
+---------------------+
| yearmonthday(now()) |
+---------------------+
| 20210315            |
+---------------------+

TIMEDIFF()

Calculates the difference between two timestamps in the format hh:ii:ss.

select timediff(1615787586, 1613787583);
+----------------------------------+
| timediff(1615787586, 1613787583) |
+----------------------------------+
| 555:33:23                        |
+----------------------------------+

DATEDIFF()

Calculates the number of days between two given timestamps.

select datediff(1615787586, 1613787583);
+----------------------------------+
| datediff(1615787586, 1613787583) |
+----------------------------------+
|                               23 |
+----------------------------------+

DATE()

Formats the date part from a timestamp argument as a string in YYYY-MM-DD format.

select date(now());
+-------------+
| date(now()) |
+-------------+
| 2023-08-02  |
+-------------+

TIME()

Formats the time part from a timestamp argument as a string in HH:MM:SS format.

select time(now());
+-------------+
| time(now()) |
+-------------+
| 15:21:27    |
+-------------+

DATE_FORMAT()

Returns a formatted string based on the provided date and format arguments. The format argument uses the same specifiers as the strftime function. For convenience, here are some common format specifiers:

  • %Y - Four-digit year
  • %m - Two-digit month (01-12)
  • %d - Two-digit day of the month (01-31)
  • %H - Two-digit hour (00-23)
  • %M - Two-digit minute (00-59)
  • %S - Two-digit second (00-59)
  • %T - Time in 24-hour format (%H:%M:%S)

Note that this is not a complete list of the specifiers. Please consult the documentation for strftime() for your operating system to get the full list.

SELECT DATE_FORMAT(NOW(), 'year %Y and time %T');
+------------------------------------------+
| DATE_FORMAT(NOW(), 'year %Y and time %T') |
+------------------------------------------+
| year 2023 and time 11:54:52              |
+------------------------------------------+

This example formats the current date and time, displaying the four-digit year and the time in 24-hour format.