SQL Snippets

Frammenti di timestamp Unix in SQL

Esempi SQL per lavorare con i timestamp Unix in MySQL, PostgreSQL e SQLite, inclusi i valori epoch attuali, la conversione in date e la riconversione in timestamp.

JavaScript Python PHP Go Java C#Ruby Rust SQL C Perl

MySQL — current timestamp

-- Seconds
UNIX_TIMESTAMP()

-- Milliseconds (MySQL 8.0+)
ROUND(UNIX_TIMESTAMP(NOW(3)) * 1000)

MySQL — timestamp to datetime

FROM_UNIXTIME(1700000000)
FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%S')

MySQL — datetime to timestamp

UNIX_TIMESTAMP('2023-11-15 06:13:20')

PostgreSQL — current timestamp

-- Seconds (integer)
EXTRACT(EPOCH FROM NOW())::BIGINT

-- Milliseconds
ROUND(EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT

PostgreSQL — timestamp to timestamptz

TO_TIMESTAMP(1700000000)
TO_TIMESTAMP(1700000000) AT TIME ZONE 'America/New_York'

PostgreSQL — datetime to epoch

EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2023-11-15T06:13:20Z')::BIGINT

SQLite — current timestamp

strftime('%s', 'now')  -- seconds

SQLite — timestamp to datetime

datetime(1700000000, 'unixepoch')
datetime(1700000000, 'unixepoch', 'localtime')

Basi dei timestamp in SQL

Ogni database espone helper per i timestamp Unix con nomi leggermente diversi. MySQL usa UNIX_TIMESTAMP(), PostgreSQL può estrarre i secondi epoch da now() e SQLite usa comunemente strftime('%s', 'now').

Note sulla conversione nel database

Memorizza i timestamp come interi quando ti servono confronti numerici veloci, o usa tipi timestamp nativi quando ti servono funzioni di fuso e data a livello di database. Sii esplicito sull'UTC ai confini di import ed export.

Note di produzione per SQL

La scelta del timestamp nel database dovrebbe corrispondere a come verrà interrogato il valore. I secondi Unix interi sono semplici per finestre di log e scansioni di intervallo. I tipi timestamp nativi sono migliori quando il database deve raggruppare per giorno di calendario, troncare al mese o applicare funzioni con fuso. Evita di mischiare le due rappresentazioni senza una regola di conversione chiara.

Usa BIGINT invece di INT quando memorizzi timestamp in millisecondi
Usa UTC per i timestamp importati così i server applicativi concordano sui confini
Usa intervalli semiaperti come created_at >= start and created_at < end
Indicizza esattamente la colonna usata nei filtri di intervallo, che siano secondi epoch o un tipo timestamp

Conversione di datetime in SQL: GETDATE, CONVERT, CAST

Gli utenti di SQL Server che cercano «datetime convert in sql», «getdate sql», «sql conversion date», «sql getdate date» o «convert date timestamp to date» di solito vogliono gli helper T-SQL GETDATE(), CONVERT() e CAST(). MySQL e PostgreSQL usano built-in diversi, ma l'operazione è la stessa: trasformare un intero Unix in un datetime, o un datetime nell'intero Unix corrispondente.

SQL Server GETDATE() — datetime corrente nel fuso locale del server; usa SYSUTCDATETIME() per 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()) rimuove l'ora; CAST('2024-03-15T14:30:00' AS datetime2) analizza ISO 8601
MySQL: SELECT UNIX_TIMESTAMP(NOW()); epoca corrente; FROM_UNIXTIME(1700000000) per l'inverso
PostgreSQL: SELECT EXTRACT(EPOCH FROM now()); epoca corrente; to_timestamp(1700000000) per l'inverso
SQLite: SELECT strftime('%s','now'); epoca corrente; datetime(1700000000, 'unixepoch') per l'inverso
SQL CAST AS DATE / sql cast as date — CAST(GETDATE() AS date) rimuove la parte oraria e restituisce solo YYYY-MM-DD
SQL CAST datetime to date / sql cast datetime to date — CAST(your_datetime AS date) mantiene solo la data di calendario
SQL CONVERT datetime / sql convert datetime — CONVERT(varchar, GETDATE(), 120) restituisce "YYYY-MM-DD HH:MI:SS"; lo stile 126 restituisce ISO 8601
SQL format date convert / sql format date convert — SQL Server: FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') per un layout personalizzato, o CONVERT(varchar, GETDATE(), 126) per ISO 8601
SQL CAST AS integer / sql cast as integer — CAST(value AS int) — utile per valori epoch memorizzati in colonne numeriche
SQL convert from datetime to date / sql convert from datetime to date — CAST(dt AS date) o CONVERT(date, dt) rimuove la parte oraria
SQL convert integer / sql convert integer — CAST(value AS bigint) per timestamp a 13 cifre in ms che superano INT
SQL datetime to date / sql datetime to date — CAST(your_datetime AS date) restituisce solo YYYY-MM-DD

FAQ

I millisecondi epoch dovrebbero essere memorizzati in INT?: No. I moderni timestamp in millisecondi sono di 13 cifre e non entrano in sicurezza in un INT a 32 bit. Usa BIGINT o un tipo timestamp nativo.
I timestamp dei database sono sempre UTC?: No. Il comportamento dipende dal tipo di database e dal fuso della sessione. Sii esplicito su UTC quando importi, esporti e confronti valori tra sistemi.