Date Functions
7 methodsFunctions for working with dates and times (MySQL dialect unless noted).
NOW()Return the current date and time as a single value (MySQL / PostgreSQL).
Returns
datetime
Example
sql
SELECT NOW();
-- e.g. '2024-06-15 14:30:00'
INSERT INTO events (created_at) VALUES (NOW());CURDATE()Return the current date (no time). MySQL function. Use CURRENT_DATE in standard SQL.
Returns
date
Example
sql
SELECT CURDATE();
-- e.g. '2024-06-15'
SELECT CURRENT_DATE; -- standard SQL / PostgreSQLDATE_ADD(date, INTERVAL expr unit)Add an interval to a date. (MySQL; use date + INTERVAL '...' in PostgreSQL.)
Parameters
| Name | Type | Description |
|---|---|---|
| date | date | Start date. |
| expr | int | Interval amount. |
| unit | keyword | Unit (DAY, MONTH, YEAR, ...). |
Returns
datetime
Example
sql
-- MySQL
SELECT DATE_ADD('2024-06-15', INTERVAL 7 DAY);
-- '2024-06-22'
SELECT DATE_ADD('2024-06-15', INTERVAL 1 MONTH);
-- '2024-07-15'DATE_SUB(date, INTERVAL expr unit)Subtract an interval from a date. (MySQL.)
Parameters
| Name | Type | Description |
|---|---|---|
| date | date | Start date. |
| expr | int | Interval amount. |
| unit | keyword | Unit (DAY, MONTH, YEAR, ...). |
Returns
datetime
Example
sql
SELECT DATE_SUB('2024-06-15', INTERVAL 30 DAY);
-- '2024-05-16'
SELECT DATE_SUB('2024-06-15', INTERVAL 1 YEAR);
-- '2023-06-15'DATEDIFF(date1, date2)Return date1 - date2 in days. (MySQL; arguments reversed in SQL Server.)
Parameters
| Name | Type | Description |
|---|---|---|
| date1 | date | First date. |
| date2 | date | Second date. |
Returns
int
Example
sql
-- MySQL: DATEDIFF(date1, date2) = date1 - date2
SELECT DATEDIFF('2024-06-15', '2024-06-01'); -- 14
-- SQL Server: DATEDIFF(day, date2, date1)
SELECT DATEDIFF(day, '2024-06-01', '2024-06-15'); -- 14DATE_FORMAT(date, format)Format a date according to a format string. (MySQL; TO_CHAR in PostgreSQL.)
Parameters
| Name | Type | Description |
|---|---|---|
| date | date | Date to format. |
| format | string | Format string (%Y, %m, %d, ...). |
Returns
string
Example
sql
-- MySQL
SELECT DATE_FORMAT('2024-06-15', '%Y/%m/%d');
-- '2024/06/15'
-- PostgreSQL:
SELECT TO_CHAR(DATE '2024-06-15', 'YYYY/MM/DD');EXTRACT(unit FROM date)Extract a part of a date (YEAR, MONTH, DAY, HOUR, ...). Standard SQL.
Parameters
| Name | Type | Description |
|---|---|---|
| unit | keyword | Part to extract. |
| date | date | Source date. |
Returns
int
Example
sql
SELECT EXTRACT(YEAR FROM '2024-06-15'); -- 2024
SELECT EXTRACT(MONTH FROM '2024-06-15'); -- 6
SELECT EXTRACT(DAY FROM '2024-06-15'); -- 15