Current epoch — epochtime.tools · A precision instrument for time

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.

TaskPostgreSQLMySQLSQL ServerSQLiteBigQuerySnowflakeSpark 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 secondsfloor(extract(epoch FROM now()))::bigintUNIX_TIMESTAMP()DATEDIFF_BIG(SECOND, '1970-01-01', SYSUTCDATETIME())unixepoch()2UNIX_SECONDS(CURRENT_TIMESTAMP())DATE_PART(epoch_second, CURRENT_TIMESTAMP())unix_timestamp()
Unix seconds to timestampto_timestamp(1700000000)3FROM_UNIXTIME(1700000000)4DATEADD(SECOND, 1700000000, '1970-01-01')5datetime(1700000000, 'unixepoch')TIMESTAMP_SECONDS(1700000000)TO_TIMESTAMP_NTZ(1700000000, 0)timestamp_seconds(1700000000)1
Unix milliseconds to timestampto_timestamp(1700000000123 / 1000.0)3FROM_UNIXTIME(1700000000123 / 1000)4DATEADD(MILLISECOND, 1700000000123 % 1000, DATEADD(SECOND, 1700000000123 / 1000, '1970-01-01'))5strftime('%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 secondsfloor(extract(epoch FROM ts))::bigint6UNIX_TIMESTAMP(ts)7DATEDIFF_BIG(SECOND, '1970-01-01', ts)unixepoch(ts)2UNIX_SECONDS(ts)DATE_PART(epoch_second, ts)unix_timestamp(ts)1
Format and parse
Format as YYYY-MM-DD HH:MM:SSto_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 timestampto_timestamp('2026-11-17 09:30:45', 'YYYY-MM-DD HH24:MI:SS')3STR_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')8PARSE_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 daysts + 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 monthts + INTERVAL '1 month'DATE_ADD(ts, INTERVAL 1 MONTH)DATEADD(MONTH, 1, ts)datetime(ts, '+1 month')9DATETIME_ADD(DATETIME(ts), INTERVAL 1 MONTH)10DATEADD(month, 1, ts)ts + INTERVAL 1 MONTH
Days between two timestampsb::date - a::dateDATEDIFF(b, a)11DATEDIFF(DAY, a, b)11julianday(b) - julianday(a)12TIMESTAMP_DIFF(b, a, DAY)13DATEDIFF(day, a, b)11datediff(b, a)11
Seconds between two timestampsextract(epoch FROM (b - a))TIMESTAMPDIFF(SECOND, a, b)DATEDIFF(SECOND, a, b)unixepoch(b) - unixepoch(a)2TIMESTAMP_DIFF(b, a, SECOND)DATEDIFF(second, a, b)unix_timestamp(b) - unix_timestamp(a)1
Truncate and boundaries
Start of the daydate_trunc('day', ts)DATE(ts)CAST(ts AS date)14datetime(ts, 'start of day')TIMESTAMP_TRUNC(ts, DAY)DATE_TRUNC('day', ts)date_trunc('DAY', ts)
Start of the monthdate_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')::dateLAST_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 numberextract(dow FROM ts)15DAYOFWEEK(ts)16DATEPART(WEEKDAY, ts)17strftime('%w', ts)15EXTRACT(DAYOFWEEK FROM ts)16DAYOFWEEK(ts)18dayofweek(ts)16
ISO week numberextract(week FROM ts)WEEK(ts, 3)DATEPART(ISO_WEEK, ts)no direct function19EXTRACT(ISOWEEK FROM ts)WEEKISO(ts)weekofyear(ts)
UTC to New York timets AT TIME ZONE 'America/New_York'20CONVERT_TZ(ts, 'UTC', 'America/New_York')21ts AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'22no direct function23DATETIME(ts, 'America/New_York')CONVERT_TIMEZONE('UTC', 'America/New_York', ts)24from_utc_timestamp(ts, 'America/New_York')

Notes

  1. Spark SQL shows timestamps in the session time zone (spark.sql.session.timeZone). Set it to UTC to see UTC.
  2. unixepoch() needs SQLite 3.38 or later. On older versions use CAST(strftime('%s', …) AS INTEGER).
  3. Returns timestamptz, shown in the session time zone. Add AT TIME ZONE 'UTC' for a UTC timestamp without a time zone.
  4. 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).
  5. 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')).
  6. Written for timestamptz. For timestamp without time zone, the value is treated as if it were UTC.
  7. UNIX_TIMESTAMP reads the value in the session time zone. For a UTC value use TIMESTAMPDIFF(SECOND, '1970-01-01', ts).
  8. 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.
  9. SQLite does not clamp to the end of the month. 2026-01-31 plus 1 month is 2026-03-03.
  10. 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.
  11. Counts calendar-day boundaries and ignores the time of day, so 23:59 to 00:01 the next day is 1.
  12. julianday gives fractional days. Wrap it in CAST(… AS INTEGER) for whole days.
  13. TIMESTAMP_DIFF supports units up to DAY. For months or years use DATE_DIFF.
  14. SQL Server 2022 and later also has DATETRUNC(day, ts), which keeps the original type.
  15. Sunday is 0.
  16. Sunday is 1.
  17. Sunday is 1 under the US English default, but the result depends on SET DATEFIRST.
  18. Sunday is 0 under the default WEEK_START. With WEEK_START set to 1, Sunday is 7.
  19. SQLite has no ISO week function. strftime('%W') counts from the first Monday and is not ISO.
  20. On a timestamptz this returns the New York wall-clock time as a timestamp without a time zone.
  21. Named zones need the MySQL time zone tables to be loaded. Otherwise use an offset such as '-05:00'.
  22. SQL Server uses Windows time zone names, not IANA names. The first AT TIME ZONE 'UTC' tags the value as UTC.
  23. SQLite has no time zone database. The 'localtime' modifier converts only to the server's own zone.
  24. 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 DATEADD takes an int, so adding Unix seconds past January 2038 overflows. DATEDIFF_BIG exists 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

More on each database

PostgreSQLMySQLSQL ServerSQLiteBigQuerySnowflakeSpark

Pulling dates out of JSON columns? jsontools.tools formats and validates it.

Last updated: