Migrering av datoformat i database
Scenarioside for skjemamigreringer og ETL-normalisering mellom SQL datetime, Unix-tidsstempler og API-datoformater.
Normalized ISO:
-
Konvertered Output:
-
Migreringssjekkliste
- Freeze source timezone assumptions before any conversion (prefer UTC baseline).
- Build a mapping table for source/target columns and expected output type.
- Run conversion on a sampled slice and compare old/new values by checksum.
- Validate daylight saving boundaries and month-end transitions.
- Roll out in stages with dual-write or shadow-read strategy to avoid silent drift.
-- Example: SQL datetime to unix seconds (MySQL)
UPDATE events
SET created_at_unix = UNIX_TIMESTAMP(created_at_datetime)
WHERE created_at_unix IS NULL;
-- Example: verify spot checks
SELECT id, created_at_datetime, created_at_unix
FROM events
ORDER BY id DESC
LIMIT 20;Relaterte verktøy: Date Konverterer, Batch epoch-konverterer, Kalkulator for arbeidsdager
FAQ
What is the safest way to migrate datetime columns?
Normalize to UTC first, migrate with deterministic functions, and verify with sampled diff checks.
How do I avoid timezone drift after migration?
Store canonical UTC values and localize only at presentation layer.
Can I migrate from SQL datetime to Unix timestamp?
Yes. Use database conversion functions and validate edge cases around DST and leap periods.