Skip to content

Instantly share code, notes, and snippets.

@leafac
Created August 25, 2021 13:22
Show Gist options
  • Save leafac/b0e156e312043f3f121fe2f7f8771665 to your computer and use it in GitHub Desktop.
Save leafac/b0e156e312043f3f121fe2f7f8771665 to your computer and use it in GitHub Desktop.
Notes on Timezones in Node.js & SQLite Applications
  • SQLite’s datetime functions are all UTC, but they don’t explicitly include the timezone information
  • In JavaScript (browsers & Node.js) datetimes without explicit timezone information are interpreted as local time
  • In servers (for example, DigitalOcean & GitHub Actions machines) the timezone is usually set to UTC
  • In development, the timezone is set to the user’s timezone
  • We can change the timezone for the Node.js process in macOS/Linux with the TZ=UTC environment variable
  • On Windows, the only solution is to temporarily change the OS timezone
  • This sounds too heavy-handed and storing datetimes without an explicit timezone seems like a bad idea, anyway
  • So the best solution that works everywhere is to avoid using SQLite datetime functions for generating values that will be stored in the database (it’s still fine to use them for queries)
  • To be exact, I’m talking about SQLite’s CURRENT_TIMESTAMP, datetime() and so forth
  • The correct way of doing it is using the more generic SQLite datetime function and provide the explicit format we want: strftime('%Y-%m-%dT%H:%M:%fZ')
  • This matches the output of JavaScript’s new Date().toISOString()
  • (Maybe including the fractions isn’t too reliable on computers that are connected to NTP servers over the internet, and having them in the values may indicate that we’re confident about this information, but we’ll ignore that…)
  • Other facts
    • Always store datetimes in UTC
    • Safari’s new Date() doesn’t handle dates like "2021-03-31 16:15", but insists on the "T", as in "2021-03-31T16:15"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment