SQL Unix Timestamp Snippets

SQL timestamp basics

Each database exposes Unix timestamp helpers with slightly different names. MySQL uses UNIX_TIMESTAMP(), PostgreSQL can extract epoch seconds from now(), and SQLite commonly uses strftime('%s', 'now').

Database conversion notes

Store timestamps as integers when you need fast numeric comparisons, or use native timestamp types when you need database-level timezone and date functions. Be explicit about UTC at import and export boundaries.

SQL production notes

Database timestamp choices should match how the value will be queried. Integer Unix seconds are simple for log windows and range scans. Native timestamp types are better when the database must group by calendar day, truncate to month, or apply timezone-aware functions. Avoid mixing both representations without a clear conversion rule.

• Use BIGINT rather than INT when storing millisecond timestamps
• Use UTC for imported timestamps so application servers agree on boundaries
• Use half-open ranges such as created_at >= start and created_at < end
• Index the exact column used in range filters, whether it is epoch seconds or a timestamp type

Datetime convert in SQL: GETDATE, CONVERT, CAST

SQL Server users searching "datetime convert in sql", "getdate sql", "sql conversion date", "sql getdate date", or "convert date timestamp to date" typically want the T-SQL helpers GETDATE(), CONVERT(), and CAST(). MySQL and PostgreSQL use different built-ins but the operation is the same: turn a Unix integer into a datetime, or a datetime into the matching Unix integer.

• SQL Server GETDATE() — current datetime in the server local zone; use SYSUTCDATETIME() for UTC
• SQL Server epoch → datetime: DATEADD(SECOND, 1700000000, '1970-01-01T00:00:00')
• SQL Server datetime → epoch: DATEDIFF_BIG(SECOND, '1970-01-01', GETUTCDATE())
• SQL Server CONVERT/CAST date↔datetime: CONVERT(date, GETDATE()) drops time; CAST('2024-03-15T14:30:00' AS datetime2) parses ISO 8601
• MySQL: SELECT UNIX_TIMESTAMP(NOW()); current epoch; FROM_UNIXTIME(1700000000) for the reverse
• PostgreSQL: SELECT EXTRACT(EPOCH FROM now()); current epoch; to_timestamp(1700000000) for the reverse
• SQLite: SELECT strftime('%s','now'); current epoch; datetime(1700000000, 'unixepoch') for the reverse
• SQL CAST AS DATE / sql cast as date — CAST(GETDATE() AS date) drops the time portion, returns just YYYY-MM-DD
• SQL CAST datetime to date / sql cast datetime to date — CAST(your_datetime AS date) keeps only the calendar date
• SQL CONVERT datetime / sql convert datetime — CONVERT(varchar, GETDATE(), 120) returns "YYYY-MM-DD HH:MI:SS"; style 126 returns ISO 8601
• SQL format date convert / sql format date convert — SQL Server: FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') for custom layout, or CONVERT(varchar, GETDATE(), 126) for ISO 8601
• SQL CAST AS integer / sql cast as integer — CAST(value AS int) — useful for epoch values stored in numeric columns
• SQL convert from datetime to date / sql convert from datetime to date — CAST(dt AS date) or CONVERT(date, dt) drops the time portion
• SQL convert integer / sql convert integer — CAST(value AS bigint) for 13-digit ms timestamps that overflow INT
• SQL datetime to date / sql datetime to date — CAST(your_datetime AS date) returns just YYYY-MM-DD