convert unix timestamp to H2 timestamp

Question

How do I convert unix timestamp value like 1348560343598 to H2 Timestamp?

One of my tables contains these unix timestamps in a BIGINT(19) column and I need to convert them to a column of type TIMESTAMP.

score 12 · Accepted Answer · answered Apr 15 '13 at 10:29

12

Ok, using the following formula works:

select DATEADD('SECOND', 1348560343, DATE '1970-01-01')

Just remember to divide the timestamp with 1000. Using 'MILLISECOND' doesn't work, you will get Numeric value out of range.

answered Apr 15 '13 at 10:29

vertti

2

Millisecond doesn't work: yes, I ran into the same problem. It's actually a limitation of `Calendar.add(int field, int amount)`, which is used internally. H2 doesn't use `java.util.Calendar` any longer for most date / time features, because of problems with timezones, but in this case it is still used. – Thomas Mueller Apr 15 '13 at 12:44
1

this actually coverts it to JVM time (so you cannot force it to be UTC which is more meaningful for storing timestamps in a database). – marios Sep 14 '17 at 00:40

1 Answers1