how to convert DATE to epoch

Question

Question

Gautam Rishi · Jun 22, 2023

I am having a datatype DATE value from db 41966. And I want to convert it to epoch time.
Also, in case of DATETIME value how can I convert it to epoch.

Discussion (7)1

Log in or sign up to continue

John Murray · Jun 23, 2023

Did you find this when you searched for "epoch" here on DC?

https://community.intersystems.com/post/how-convert-current-date-and-tim...

0 0

Robert Cemper · Jul 5, 2023

#1 is correct

Your calculation #2 is seriously wrong.
reason
reading documentation you see
dformat -2

$ZDATETIME returns an integer specifying the count of seconds from a platform-specific origin date/time. This is the value returned by the time() library function, as defined in the ISO C Programming Language Standard. For example, on POSIX-compliant systems this value is the count of seconds from January 1, 1970 00:00:00 UTC

And that's the mistake:
Your BirthDate is obviously considered as LOCAL time
And therefore the difference you see reflects the time offset of your machine to UTC

-19800 sec => -5.5 hrs
system variable $ZTZ will show your offset to UTC in minutes => -330
my guess: your machine is running at local time in India

1 0

score 0 · Answer 1 · 2023-06-22T15:33:53-04:00

Steve Clay · Jun 22, 2023

Check out the methods in %Library.UTC, there may be something in there you could use.

0 0

score 0 · Answer 2 · 2023-06-23T00:41:42-04:00

Gautam Rishi · Jun 23, 2023

I didn't get any standard methods in this library. If any other you can suggest?

0 0

score 0 · Answer 3 · 2023-07-05T11:29:11-04:00

@John Murray
thanks for sharing link. I tried the both 2 approach
1. Database query with DATEDIFF(s, '1970-01-01 00:00:00', BirthDate) => OUTPUT = -54777600

2. $ZDATETIME(BirthDate, -2) => OUTPUT = -54797400

where BirthDate = 46483

which is not same. So I am confused if the query result is wrong or 2 approach result.

score 0 · Answer 4 · 2023-07-06T03:45:49-04:00

so you mean to say that sql query DATEDIFF() method is right to be used in code instead of ZDATETIME()

score 0 · Answer 5 · 2023-07-06T06:51:35-04:00

You still didn't understand the difference:

- DATDEDIFF() works on local times eg. "1970-01-01" is your locale time (eg. UTC+5.5 hrs)
- $ZDT(...,-2) uses by definition 1970-01-01 UTC

you should know from what time zone you get your input from