Posted on

Use UTC/epoch for Date/Time

Always use UTC for storing date/time information. Have the browser convert the epoch timestamp into the local time like so:

function localDate(timestamp) {
    let d = new Date();
    d.setTime(timestamp);
    return d.toLocaleDateString();
}

Docker MySQL instances are configured to UTC. MySQL uses seconds for epoch while Javascript uses milliseconds, so divide by 1000 when going from Javascript to MySQL and multiply when going the other direction. When storing timestamps, use the getTime method on the Date object using Javascript, and in MySQL, use the FROM_UNIXTIME function (after dividing by 1000). When retrieving timestamps, use the unix_timestamp function (and multiply by 1000). I’m surprised that Javascript and SQL don’t take epoch values by default.