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

Frequently checked SQL details

Should epoch milliseconds be stored in INT?

No. Modern millisecond timestamps are 13 digits and do not fit safely in a 32-bit INT. Use BIGINT or a native timestamp type.

Are database timestamps always UTC?

No. Behavior depends on the database type and session timezone. Be explicit about UTC when importing, exporting, and comparing values across systems.