Datetime: epoch into Date time format

Sven_Kutscher · 09-23-2019 05:50 AM

Hello, I have some problems with the converting of “epoch/unix” into a datetime format

Example:
epoch: 1566162000000
result datetime: 19.08.2019 00:00:00

Problem:
The time is missing and I do not know why.

Formel
DATETIME([epoch]/86400/1000+“01/01/1970 00:00”)

Thanks a lot

Steve

A DateTime plus a Number advances the DateTime by Number days, not seconds.

Sven_Kutscher

Danke / Thanks

Sven_Kutscher

I need you support…

What I have:
Epoch:1566179000000 (milliseconds)
Datetime: Monday, August 19, 2019 1:43:20

Result with following formel:
8/19/2019 2:00:00 AM
DATETIME( Number([Epoch]/86400/1000 ) + Datetime(Day( “01.01.1970”)))

Problem: The time is not correct

Thanks for helping out

Steve

Try:

Datetime(“01/01/1970”) + Number([Epoch] / 86400 / 1000)

Literal Date values in expressions must be formatted as MM/DD/YYYY; other formats are not recognized as Date values in expressions.

Sven_Kutscher

Test it: 8/19/2019 12:00:00 AM Time not ok

Steve

What should the correct result be?

Sven_Kutscher · Sven_Kutscher: Monday, August 19, 2019 1:43:20 Monday, August 19, 2019 1:43:20

Monday, August 19, 2019 1:43:20

Steve

Ah, then we’re back to my original response:

Where do you expect the time to come from?

Sven_Kutscher

IF you convert this “epoch” 1566179280000 via https://www.epochconverter.com/
Than you get the “Date time” as result.

thx

Steve

AppSheet is not epochconverter.com; AppSheet doesn’t work the same way.

Sven_Kutscher

ok thanks a lot

ntrejo

I know this is an old post, but I have been working on a similar problem: converting a date saved in the database as unix time stamp in seconds into an actual datetime in AppSheet.

The formula I achieved (after seveal attempts) is the following:

DATETIME(“01/01/1970”) +
FLOOR([reception_date] / (24*60*60)) +
(
  TIME(
    CONCATENATE(
      FLOOR( MOD([reception_date], 24*60*60) / (60*60) ),
      ":", 
      FLOOR( MOD([reception_date], 60*60) / 60 ),
      ":", 
      FLOOR( MOD([reception_date], 60) )
    )
  )
  - "00:00:00"
)

Explanation

DATETIME(“01/01/1970”): starting of Unix Epoch on January 1st, 1970 at UTC
FLOOR([reception_date] / (24*60*60)): number of whole days
FLOOR( MOD([reception_date], 24*60*60) / (60*60) ): hours
FLOOR( MOD([reception_date], 60*60) / 60 ),: minutes
FLOOR( MOD([reception_date], 60) ): seconds
CONCATENATE( <hours>, ":", <minutes>, ":", <seconds>): get a text representing the time
TIME(): convert text to time
- "00:00:00": convert time to duration (this is a trick I learnt from @Steve here: TIME() | AppSheet Help Center)

I hope it can be helpfull for next creators.

alirezai

that works thanks