Furlo's Playground

Táto stránka je iba na čítanie. Môžete si prehliadnuť zdrojový kód, ale nemôžete ho meniť. Opýtajte sa správcu, ak si myslíte, že niečo nie je v poriadku.
====== 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 [[wp>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 GMT-4 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:
  - Change the format of all your dates to ''Plain text'' (in ''Format → Number →'' menu)
  - Change the locale of your sheet into ''United Kingdom'' (in ''File → Spreadsheet settings'' menu)
  - Change the format of all your dates back to ''Date''
  - 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 timezone-manipulation 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 GMT-4 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!


~~socialite~~

===== See also =====

  * [[https://www.epochconverter.com/|EpochConverter.com]], a splendid online tool for conversions between timestamp and time, both GMT and local
  * [[https://infoinspired.com/google-docs/spreadsheet/convert-unix-timestamp-to-local-datetime-in-google-sheets/|Convert Unix Timestamp to Local DateTime and Vice Versa in Google Sheets]], the original article from which I have learned the math behind.
  * [[https://support.google.com/docs/thread/33346427?hl=en|Sheets not recognizing dates: Error DATEVALUE parameter '14/2/2020' cannot be parsed to date/time]], a helpful Google support thread when Google sheets reject to parse your date strings


===== Comments =====

~~DISQUS~~
Furlo's Playground

Nástroje používateľa

Nástoje správy stránok

Nástoje stránky
