SQL date and time cookbook
The same date task written for PostgreSQL, MySQL, SQL Server, SQLite, BigQuery, Snowflake and Spark SQL. Epoch conversion, adding time, differences, truncation, formatting and time zones, ready to copy.
In every example ts, a and b are timestamp columns holding UTC values (timestamptz in PostgreSQL), and numbers such as 1700000000 are Unix seconds. Click a query to copy it. Numbers in small type point to the notes below the table.
| Task | PostgreSQL | MySQL | SQL Server | SQLite | BigQuery | Snowflake | Spark SQL |
|---|---|---|---|---|---|---|---|
| Now and epoch | |||||||
| Current timestamp (UTC) | now() AT TIME ZONE 'UTC' | UTC_TIMESTAMP() | SYSUTCDATETIME() | datetime('now') | CURRENT_TIMESTAMP() | SYSDATE() | current_timestamp()1 |
| Current Unix time in seconds | floor(extract(epoch FROM now()))::bigint | UNIX_TIMESTAMP() | DATEDIFF_BIG(SECOND, '1970-01-01', SYSUTCDATETIME()) | unixepoch()2 | UNIX_SECONDS(CURRENT_TIMESTAMP()) | DATE_PART(epoch_second, CURRENT_TIMESTAMP()) | unix_timestamp() |
| Unix seconds to timestamp | to_timestamp(1700000000)3 | FROM_UNIXTIME(1700000000)4 | DATEADD(SECOND, 1700000000, '1970-01-01')5 | datetime(1700000000, 'unixepoch') | TIMESTAMP_SECONDS(1700000000) | TO_TIMESTAMP_NTZ(1700000000, 0) | timestamp_seconds(1700000000)1 |
| Unix milliseconds to timestamp | to_timestamp(1700000000123 / 1000.0)3 | FROM_UNIXTIME(1700000000123 / 1000)4 | DATEADD(MILLISECOND, 1700000000123 % 1000, DATEADD(SECOND, 1700000000123 / 1000, '1970-01-01'))5 | strftime('%Y-%m-%d %H:%M:%f', 1700000000123 / 1000.0, 'unixepoch') | TIMESTAMP_MILLIS(1700000000123) | TO_TIMESTAMP_NTZ(1700000000123, 3) | timestamp_millis(1700000000123)1 |
| Timestamp to Unix seconds | floor(extract(epoch FROM ts))::bigint6 | UNIX_TIMESTAMP(ts)7 | DATEDIFF_BIG(SECOND, '1970-01-01', ts) | unixepoch(ts)2 | UNIX_SECONDS(ts) | DATE_PART(epoch_second, ts) | unix_timestamp(ts)1 |
| Format and parse | |||||||
| Format as YYYY-MM-DD HH:MM:SS | to_char(ts, 'YYYY-MM-DD HH24:MI:SS') | DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s') | CONVERT(varchar(19), ts, 120) | strftime('%Y-%m-%d %H:%M:%S', ts) | FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts) | TO_CHAR(ts, 'YYYY-MM-DD HH24:MI:SS') | date_format(ts, 'yyyy-MM-dd HH:mm:ss') |
| Parse text to a timestamp | to_timestamp('2026-11-17 09:30:45', 'YYYY-MM-DD HH24:MI:SS')3 | STR_TO_DATE('2026-11-17 09:30:45', '%Y-%m-%d %H:%i:%s') | TRY_CONVERT(datetime2, '2026-11-17 09:30:45', 120) | datetime('2026-11-17 09:30:45')8 | PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2026-11-17 09:30:45') | TO_TIMESTAMP('2026-11-17 09:30:45', 'YYYY-MM-DD HH24:MI:SS') | to_timestamp('2026-11-17 09:30:45', 'yyyy-MM-dd HH:mm:ss') |
| Add and subtract | |||||||
| Add 7 days | ts + INTERVAL '7 days' | DATE_ADD(ts, INTERVAL 7 DAY) | DATEADD(DAY, 7, ts) | datetime(ts, '+7 days') | TIMESTAMP_ADD(ts, INTERVAL 7 DAY) | DATEADD(day, 7, ts) | ts + INTERVAL 7 DAYS |
| Add 1 month | ts + INTERVAL '1 month' | DATE_ADD(ts, INTERVAL 1 MONTH) | DATEADD(MONTH, 1, ts) | datetime(ts, '+1 month')9 | DATETIME_ADD(DATETIME(ts), INTERVAL 1 MONTH)10 | DATEADD(month, 1, ts) | ts + INTERVAL 1 MONTH |
| Days between two timestamps | b::date - a::date | DATEDIFF(b, a)11 | DATEDIFF(DAY, a, b)11 | julianday(b) - julianday(a)12 | TIMESTAMP_DIFF(b, a, DAY)13 | DATEDIFF(day, a, b)11 | datediff(b, a)11 |
| Seconds between two timestamps | extract(epoch FROM (b - a)) | TIMESTAMPDIFF(SECOND, a, b) | DATEDIFF(SECOND, a, b) | unixepoch(b) - unixepoch(a)2 | TIMESTAMP_DIFF(b, a, SECOND) | DATEDIFF(second, a, b) | unix_timestamp(b) - unix_timestamp(a)1 |
| Truncate and boundaries | |||||||
| Start of the day | date_trunc('day', ts) | DATE(ts) | CAST(ts AS date)14 | datetime(ts, 'start of day') | TIMESTAMP_TRUNC(ts, DAY) | DATE_TRUNC('day', ts) | date_trunc('DAY', ts) |
| Start of the month | date_trunc('month', ts) | DATE_FORMAT(ts, '%Y-%m-01') | DATEFROMPARTS(YEAR(ts), MONTH(ts), 1) | date(ts, 'start of month') | DATE_TRUNC(DATE(ts), MONTH) | DATE_TRUNC('month', ts) | trunc(ts, 'MM') |
| Last day of the month | (date_trunc('month', ts) + INTERVAL '1 month - 1 day')::date | LAST_DAY(ts) | EOMONTH(ts) | date(ts, 'start of month', '+1 month', '-1 day') | LAST_DAY(DATE(ts)) | LAST_DAY(ts) | last_day(ts) |
| Parts and zones | |||||||
| Day of week as a number | extract(dow FROM ts)15 | DAYOFWEEK(ts)16 | DATEPART(WEEKDAY, ts)17 | strftime('%w', ts)15 | EXTRACT(DAYOFWEEK FROM ts)16 | DAYOFWEEK(ts)18 | dayofweek(ts)16 |
| ISO week number | extract(week FROM ts) | WEEK(ts, 3) | DATEPART(ISO_WEEK, ts) | no direct function19 | EXTRACT(ISOWEEK FROM ts) | WEEKISO(ts) | weekofyear(ts) |
| UTC to New York time | ts AT TIME ZONE 'America/New_York'20 | CONVERT_TZ(ts, 'UTC', 'America/New_York')21 | ts AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'22 | no direct function23 | DATETIME(ts, 'America/New_York') | CONVERT_TIMEZONE('UTC', 'America/New_York', ts)24 | from_utc_timestamp(ts, 'America/New_York') |
Notes
- Spark SQL shows timestamps in the session time zone (spark.sql.session.timeZone). Set it to UTC to see UTC.
- unixepoch() needs SQLite 3.38 or later. On older versions use CAST(strftime('%s', …) AS INTEGER).
- Returns timestamptz, shown in the session time zone. Add AT TIME ZONE 'UTC' for a UTC timestamp without a time zone.
- The result is in the session time zone. For UTC, set time_zone to '+00:00' or use DATE_ADD('1970-01-01', INTERVAL n SECOND).
- Older versions of DATEADD take the number as an int, so seconds past 2,147,483,647 (January 2038) overflow. Add days first: DATEADD(SECOND, n % 86400, DATEADD(DAY, n / 86400, '1970-01-01')).
- Written for timestamptz. For timestamp without time zone, the value is treated as if it were UTC.
- UNIX_TIMESTAMP reads the value in the session time zone. For a UTC value use TIMESTAMPDIFF(SECOND, '1970-01-01', ts).
- SQLite has no format-based parser. datetime() reads ISO 8601 text such as 2026-11-17 09:30:45 or 2026-11-17T09:30:45Z.
- SQLite does not clamp to the end of the month. 2026-01-31 plus 1 month is 2026-03-03.
- TIMESTAMP_ADD stops at DAY. Months go through DATETIME_ADD, which returns a DATETIME in UTC. Wrap it in TIMESTAMP() to get a timestamp back.
- Counts calendar-day boundaries and ignores the time of day, so 23:59 to 00:01 the next day is 1.
- julianday gives fractional days. Wrap it in CAST(… AS INTEGER) for whole days.
- TIMESTAMP_DIFF supports units up to DAY. For months or years use DATE_DIFF.
- SQL Server 2022 and later also has DATETRUNC(day, ts), which keeps the original type.
- Sunday is 0.
- Sunday is 1.
- Sunday is 1 under the US English default, but the result depends on SET DATEFIRST.
- Sunday is 0 under the default WEEK_START. With WEEK_START set to 1, Sunday is 7.
- SQLite has no ISO week function. strftime('%W') counts from the first Monday and is not ISO.
- On a timestamptz this returns the New York wall-clock time as a timestamp without a time zone.
- Named zones need the MySQL time zone tables to be loaded. Otherwise use an offset such as '-05:00'.
- SQL Server uses Windows time zone names, not IANA names. The first AT TIME ZONE 'UTC' tags the value as UTC.
- SQLite has no time zone database. The 'localtime' modifier converts only to the server's own zone.
- With a TIMESTAMP_NTZ input, the three-argument form returns TIMESTAMP_NTZ.
Five traps this table hides
- Session time zones. MySQL, Spark SQL and PostgreSQL (for timestamptz) turn instants into text using the session setting. The same query can print different times on two servers.
- SQLite month overflow.
datetime('2026-01-31', '+1 month')returns 2026-03-03. Every other database here returns 28 February. - SQL Server's int limit. On older versions
DATEADDtakes an int, so adding Unix seconds past January 2038 overflows.DATEDIFF_BIGexists for the reverse direction. - Day-of-week numbering. Sunday is 0 in PostgreSQL, SQLite and Snowflake, and 1 in MySQL, BigQuery and Spark. SQL Server depends on
SET DATEFIRST. - Boundary counting.
DATEDIFF(DAY, …)in SQL Server, MySQL, Snowflake and Spark counts date changes, not 24-hour periods.
How these were checked
The PostgreSQL 16 and SQLite 3.53 queries were run, and their results matched what the other columns describe. The MySQL, SQL Server, BigQuery, Snowflake and Spark SQL queries were checked against each vendor's own documentation but not executed here, so test them on your version before relying on them.
Questions
Which SQL function adds a month without overflowing the end of the month?
PostgreSQL, MySQL, SQL Server, Snowflake, Spark SQL and BigQuery all clamp to the last day of the target month, so 31 January plus 1 month is 28 February (29 in a leap year). SQLite does not: it normalises the invalid date and returns 3 March.
How do I get the Unix timestamp in each database?
PostgreSQL floor(extract(epoch FROM ts)), MySQL UNIX_TIMESTAMP(ts), SQL Server DATEDIFF_BIG(SECOND, '1970-01-01', ts), SQLite unixepoch(ts), BigQuery UNIX_SECONDS(ts), Snowflake DATE_PART(epoch_second, ts) and Spark SQL unix_timestamp(ts).
Why does my result change with the server's time zone?
Several functions use the session time zone: MySQL FROM_UNIXTIME and UNIX_TIMESTAMP, PostgreSQL date_trunc and to_timestamp on timestamptz, and Spark SQL timestamps. Set the session to UTC, or convert explicitly, so the same query gives the same answer on every server.
Do these queries run on every version?
Not all. unixepoch() needs SQLite 3.38, DATEDIFF_BIG and AT TIME ZONE need SQL Server 2016, timestamp_seconds needs Spark 3.1, and DATETRUNC needs SQL Server 2022.
Official references
- PostgreSQL: Date/time functions and operators
- MySQL: Date and time functions
- SQL Server: Date and time functions (Transact-SQL)
- SQLite: Date and time functions
- BigQuery: Timestamp functions
- Snowflake: Date and time functions
- Spark SQL: Datetime functions
More on each database
PostgreSQLMySQLSQL ServerSQLiteBigQuerySnowflakeSpark
Pulling dates out of JSON columns? jsontools.tools formats and validates it.
Last updated: