Nástroje používateľa

Nástoje správy stránok


Bočný panel

Sidebar


Články

Prečítané diela

Hlášky

Projekty

Tagy

Twitter


blog:odborny:2020-09-04-converting_between_unix_timestamp_and_local_date_in_google_sheets

Converting between Unix Timestamp and Local Date in Google sheets

Google sheets do not offer any straightforward way to convert from Unix Timestamp (that is, the number of seconds that passed since 1 January 1970) to local date and vice versa. If you are web admin, this is something you will sooner or later need when passing data between Google sheets and server SQL database.

Little math behind

Unix Timestamp is the number of seconds that passed since 1 January 1970, which marks the start of Unix epoch.

The “magic constant” in formulae below is 86400, which is the number of seconds in single day (24*60*60).

The second one is DATE(1970,1,1), which generates the starting day of Unix epoch. This is the reference date which has the timestamp 0. All dates before this date will have negative timestamp, while dates after it a positive one.

Converting (local) date to timestamp

First, the general formula is this:

timestamp = (localDateTime - unixEpochDate - timezoneShift)*daySeconds

In the case of 4 September 2020 9:54:25am GMT+2 (Slovakia during summer time), this translates into the following formula in Google sheets:

timestamp = ( localDateTime                     - unixEpochDate    - timezoneShift  ) * daySeconds
                ↓                                   ↓                  ↓                  ↓
timestamp = ( (DATE(2020,09,04)+time(09,54,25)) - DATE(1970,01,01) - time(02,00,00) ) * 86400

:!: Note that you need to subtract a positive GMT shift (so GMT+05:30 becomes time(05,30,00)) and, vice versa, add a negative timezone shift (so GMT4 becomes + time(04,00,00)).

When you have a cell with date string which you need to convert into timestamp, you can reference the cell directly, so the final formula will be:

= ( <cellRef> - DATE(1970,01,01) - time(02,00,00) ) * 86400
= ( $J10      - DATE(1970,01,01) - time(02,00,00) ) * 86400

:!: If you ever encounter Google Sheets refusing to accept and convert your date string with a weird Error DATEVALUE parameter '04/09/2020' cannot be parsed to date/time, try doing this:

  1. Change the format of all your dates to Plain text (in Format → Number → menu)
  2. Change the locale of your sheet into United Kingdom (in File → Spreadsheet settings menu)
  3. Change the format of all your dates back to Date
  4. Formula should work now!

Converting timestamp to (local) date

This is an exact reverse of the previous procedure, so the general formula is this:

localDateTime = (timestamp / daySeconds) + unixEpochDate + timezoneShift

So in the case of 1599206065 timestamp in GMT+2 (Slovakia during summer time), this will become in Google sheets:

localDateTime = (timestamp  / daySeconds) + unixEpochDate    + timezoneShift
                   ↓            ↓             ↓                  ↓
localDateTime = (1599206065 / 86400)      + DATE(1970,01,01) + time(02,00,00)

:!: Note that the timezonemanipulation in here is the opposite of the previous formula, so you need to add a positive GMT shift (so GMT+05:30 becomes + time(05,30,00)) and, vice versa, subtract a negative timezone shift (so GMT4 becomes time(04,00,00)).

Again, with cell reference containing the timestamp that needs to be converted, the final formula will be:

= (<cellRef> / 86400) + DATE(1970,01,01) + time(02,00,00)
= ($J10      / 86400) + DATE(1970,01,01) + time(02,00,00)

And that's it!

See also

Comments

blog/odborny/2020-09-04-converting_between_unix_timestamp_and_local_date_in_google_sheets.txt · Posledná úprava: 2020/09/04 10:48 od Róbert Toth