Equivalent of UNIX_TIMESTAMP function for H2 DB?

Question

Does anyone know any proper substitute of MySQL's UNIX_TIMESTAMP(time_val) for H2, such that applying that function on a JDBC timestamp returns a unix epoch from 00:00:00 01 Jan 1970?

Possible duplicate of [CURRENT\_TIMESTAMP in milliseconds in h2](https://stackoverflow.com/questions/30720739/current-timestamp-in-milliseconds-in-h2) — astafev.evgeny, Dec 21 '18 at 14:48

score 3 · Answer 1 · answered Dec 21 '18 at 14:49

3

DATEDIFF('SECOND', DATE '1970-01-01', CURRENT_TIMESTAMP())

as answered here: CURRENT_TIMESTAMP in milliseconds in h2

or here: https://gitlab.com/romain.rinie/h2database/issues/211

answered Dec 21 '18 at 14:49

astafev.evgeny

466
4
21

I've tried this and it doesn't seem to be accurate - there was a small diff between the result and the actual Unix timestamp. – yishaiz May 17 '20 at 20:46

yishaiz · Answer 2 · 2020-08-03T16:00:20.023

1

I've tried the other answer and the result wasn't accurate. In the other answer I get the timestamp in my local time zone (GMT+3 in my case), so it's not the same as the Unix timestamp (in milliseconds). In my answer, I get the result in UTC.

This would give you the correct current UNIX epoch:

SELECT EXTRACT (EPOCH from CURRENT_TIMESTAMP())

edited Aug 03 '20 at 16:00

answered May 17 '20 at 20:46

yishaiz

2,433
4
28
49

2

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – β.εηοιτ.βε May 17 '20 at 22:35
Thanks for the feedback - I've edited my answer to provide more context. – yishaiz May 18 '20 at 05:23

Equivalent of UNIX_TIMESTAMP function for H2 DB?

2 Answers2