Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT DATETIME('2025-02-14 20:57', '+2 hours');   -- 2025-02-14 22:57:00

Try it

SQL Normalizing and shifting order times ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Numbers, Unix time and rolling over

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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'.