Convert from timestamp to BIGINT

Openfire
1

Hallo,

I 'm struggling with an SQL query to convert a timestamp to a bigint, as this is how the table field that stores the date is defined. I have tried a number of things, but I always get errors with different error codes/states.

E.g.

INSERT INTO "PUBLIC"."OFMESSAGEARCHIVE"
("CONVERSATIONID", "SENTDATE")
VALUES (1, CONVERT (to_timestamp ('22/11/2008 20:30:40', 'DD/MM/YYYY HH:MI:SS' ), 'SQL_BIGINT') );

I tried with CAST etc.but no luck. Any hints?

Thanks.

2

which database you are using?

3

The embedded one (HSQLDB)…

4

try below one

select TO_DATE(timestamp, substring(timestamp, 11,3) * 1000000) as date, timestamp from TABLE

5

Thanks for your reply Hiren1. The correct syntax seems to be the following that works with HSQLDB:

UNIX_TIMESTAMP(TO_TIMESTAMP('Oct 26, 2017 13:27:21', 'MON DD, YYYY HH:MI:SS'))

Or if the timestamp contains milliseconds:

UNIX_TIMESTAMP(TO_TIMESTAMP('Oct 26, 2017 13:27:21:262', 'MON DD, YYYY HH:MI:SS:FFF'))

It seems that one needs to multiply the above by 1000 to get the correct value. Instead one can use UNIX_MILLIS and not multiply by 1000:

UNIX_MILLIS(TO_TIMESTAMP('Oct 26, 2017 13:27:21:262', 'MON DD, YYYY HH:MI:SS:FFF'))

If the date contains AM/PM (e.g. 'Oct 26, 2017 13:27:21:262 PM') then one needs to add 12 hours for PM.