SQL Snippets

Extraits de timestamp Unix en SQL

Exemples SQL pour travailler avec les timestamps Unix dans MySQL, PostgreSQL et SQLite, y compris les valeurs epoch actuelles, la conversion en dates et la reconversion en timestamps.

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')

Bases des horodatages en SQL

Chaque base de données expose des aides aux horodatages Unix avec des noms légèrement différents. MySQL utilise UNIX_TIMESTAMP(), PostgreSQL peut extraire les secondes epoch de now() et SQLite utilise couramment strftime('%s', 'now').

Notes de conversion en base de données

Stockez les horodatages en entiers quand vous avez besoin de comparaisons numériques rapides, ou utilisez des types d'horodatage natifs quand vous avez besoin de fonctions de fuseau et de date au niveau de la base. Soyez explicite sur l'UTC aux frontières d'import et d'export.

Notes de production pour SQL

Le choix de l'horodatage en base doit correspondre à la façon dont la valeur sera interrogée. Les secondes Unix entières sont simples pour les fenêtres de journaux et les balayages de plage. Les types d'horodatage natifs sont préférables quand la base doit grouper par jour calendaire, tronquer au mois ou appliquer des fonctions tenant compte du fuseau. Évitez de mélanger les deux représentations sans règle de conversion claire.

Utilisez BIGINT plutôt que INT pour stocker des horodatages en millisecondes
Utilisez l'UTC pour les horodatages importés afin que les serveurs d'application s'accordent sur les bornes
Utilisez des plages semi-ouvertes comme created_at >= start and created_at < end
Indexez exactement la colonne utilisée dans les filtres de plage, qu'il s'agisse de secondes epoch ou d'un type timestamp

Conversion de datetime en SQL : GETDATE, CONVERT, CAST

Les utilisateurs de SQL Server qui cherchent « datetime convert in sql », « getdate sql », « sql conversion date », « sql getdate date » ou « convert date timestamp to date » veulent généralement les aides T-SQL GETDATE(), CONVERT() et CAST(). MySQL et PostgreSQL utilisent d'autres fonctions intégrées, mais l'opération est la même : transformer un entier Unix en datetime, ou un datetime en l'entier Unix correspondant.

SQL Server GETDATE() — datetime actuel dans le fuseau local du serveur ; utilisez SYSUTCDATETIME() pour l'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()) supprime l'heure ; CAST('2024-03-15T14:30:00' AS datetime2) analyse l'ISO 8601
MySQL : SELECT UNIX_TIMESTAMP(NOW()); époque actuelle ; FROM_UNIXTIME(1700000000) pour l'inverse
PostgreSQL : SELECT EXTRACT(EPOCH FROM now()); époque actuelle ; to_timestamp(1700000000) pour l'inverse
SQLite : SELECT strftime('%s','now'); époque actuelle ; datetime(1700000000, 'unixepoch') pour l'inverse
SQL CAST AS DATE / sql cast as date — CAST(GETDATE() AS date) supprime la partie heure et renvoie seulement YYYY-MM-DD
SQL CAST datetime to date / sql cast datetime to date — CAST(your_datetime AS date) ne conserve que la date calendaire
SQL CONVERT datetime / sql convert datetime — CONVERT(varchar, GETDATE(), 120) renvoie "YYYY-MM-DD HH:MI:SS" ; le style 126 renvoie l'ISO 8601
SQL format date convert / sql format date convert — SQL Server : FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') pour une mise en forme personnalisée, ou CONVERT(varchar, GETDATE(), 126) pour l'ISO 8601
SQL CAST AS integer / sql cast as integer — CAST(value AS int) — utile pour les valeurs epoch stockées dans des colonnes numériques
SQL convert from datetime to date / sql convert from datetime to date — CAST(dt AS date) ou CONVERT(date, dt) supprime la partie heure
SQL convert integer / sql convert integer — CAST(value AS bigint) pour les horodatages ms à 13 chiffres qui dépassent INT
SQL datetime to date / sql datetime to date — CAST(your_datetime AS date) renvoie seulement YYYY-MM-DD

FAQ

Faut-il stocker les millisecondes epoch dans un INT ?: Non. Les horodatages modernes en millisecondes ont 13 chiffres et ne tiennent pas en toute sécurité dans un INT 32 bits. Utilisez BIGINT ou un type d'horodatage natif.
Les horodatages de base de données sont-ils toujours en UTC ?: Non. Le comportement dépend du type de base de données et du fuseau de session. Soyez explicite sur l'UTC lors de l'import, de l'export et de la comparaison de valeurs entre systèmes.