SQL Reference ยท Date and Time Functions
SQL DATETIME() function
Reference for SQL DATETIME(): build a normalized date-time, convert Unix timestamps and Julian days, add or subtract time, and the equivalents in other databases.
DATETIME(value, modifier, ...) returns a complete date and time as text in the form YYYY-MM-DD HH:MM:SS. It normalizes whatever you pass in, converts numbers such as Unix timestamps to readable form, and applies modifiers to move the moment forward or back.
It is the right function whenever you need both parts at once: "two hours from now", "the start of today", "when was timestamp 1739566620?". Everything is text underneath, in the ISO format that sorts chronologically.
SELECT DATETIME('2025-02-14 20:57', '+2 hours'); -- 2025-02-14 22:57:00
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
DATETIME(time_value [, modifier, ...])
| Name | Description |
|---|---|
time_value |
ISO date-time text, a date, a time, 'now', a Julian day number, or Unix seconds when combined with 'unixepoch'. |
modifier |
Optional, repeatable, applied left to right. For example '+1 day', 'start of month', 'localtime', 'utc', 'unixepoch', 'subsec'. See date modifiers. |
Returns: text YYYY-MM-DD HH:MM:SS (with fractions as .SSS when 'subsec' is used), or NULL if the value is invalid or NULL.
Dialect differences
| Database | Behavior |
|---|---|
| SQLite | DATETIME(value, modifiers...); text result in UTC unless you use 'localtime'. |
| PostgreSQL | No such function; use timestamp '2025-02-14 20:57', to_timestamp(unix_seconds) and ts + interval '2 hours'. |
| MySQL | DATETIME is a type; CAST(x AS DATETIME) converts, FROM_UNIXTIME(n) converts Unix seconds, DATE_ADD() shifts. |
| SQL Server | datetime2 is the modern type: CAST(x AS datetime2), DATEADD(hour, 2, x), and DATEADD(second, n, '1970-01-01') for Unix time. |
| Oracle | TO_DATE('2025-02-14 20:57', 'YYYY-MM-DD HH24:MI') and TO_TIMESTAMP(...); d + 2/24 adds two hours. |
-- PostgreSQL SELECT to_timestamp(1739566620) AT TIME ZONE 'UTC'; -- MySQL SELECT FROM_UNIXTIME(1739566620), DATE_ADD(NOW(), INTERVAL 2 HOUR); -- SQL Server SELECT DATEADD(hour, 2, SYSDATETIME());
Usage notes
- Unix timestamps need the
'unixepoch'modifier:DATETIME(1739566620, 'unixepoch'). Without it, the number is read as a Julian day. - UTC by default.
DATETIME('now')is UTC;DATETIME('now', 'localtime')converts to the machine's zone. Store UTC, convert on display. - Month math can overflow.
'-1 month'from March 31 asks for February 31, which rolls forward to March 3. See date modifiers for the end-of-month pattern. - Seconds precision. Fractions are dropped unless you add
'subsec'(SQLite 3.42 and later). - A date with no time becomes midnight (
00:00:00), which is handy for building range boundaries. - Comparing is safe in ISO format (
WHERE ordered_at >= DATETIME('now', '-7 days')), but not for text like'14/02/2025'.