Vardchives

Timestamp to Datetime ​

Description ​

Backfill proper DATETIME columns from legacy Unix timestamp integers — typically when adopting Laravel's created_at / updated_at / deleted_at conventions on an inherited schema.

Snippet ​

sql
UPDATE listings
SET created_at = FROM_UNIXTIME( timestamp_added ),
updated_at = FROM_UNIXTIME( timestamp_scraped ),
deleted_at = FROM_UNIXTIME( timestamp_deleted )

Notes ​

No WHERE clause, so this rewrites every row in the table. Intended as a one-shot migration, not something to re-run.

Two conversion traps worth checking before running it:

  • FROM_UNIXTIME(0) returns 1970-01-01 00:00:00, not NULL. If the legacy schema used 0 to mean "never deleted", every row lands in the soft-deleted state and disappears from your queries. Guard with NULLIF(timestamp_deleted, 0).
  • FROM_UNIXTIME(NULL) correctly returns NULL, so genuine nulls are fine as-is.

FROM_UNIXTIME interprets the integer as UTC and returns it in the session time zone, so a mismatched time_zone shifts every value. Check SELECT @@session.time_zone; first.

Verify with a SELECT before committing:

sql
SELECT timestamp_added, FROM_UNIXTIME(timestamp_added) FROM listings LIMIT 20;