CODEWITHSUNDEEP
sqlh2
somebody
somebody

Reputation: 1107

How to convert TIMESTAMP to SECONDs in H2?

I have a table

   CREATE TABLE MY_TABLE (id int, my_date timestamp);

and data:

   INSERT INTO MY_TABLE (id, my_date) VALUES (1, '2015-08-04 05:41:02')

How to select my_date in seconds: 2015-08-04 05:41:02 ---- > 1438656062 in H2 database

Upvotes: 1

Views: 13172

Answers (3)

Christian MICHON
Christian MICHON

Reputation: 2180

I would suggest the following query:

SELECT
  *,
  CAST(DATEDIFF('SECOND','1970-01-01',MY_DATE) AS BIGINT) AS UNIX_TIMESTAMP
FROM MY_TABLE ;

Note that the cast as bigint may not be needed...

This works too:

SELECT
  *,
  DATEDIFF('SECOND','1970-01-01',MY_DATE) AS UNIX_TIMESTAMP
FROM MY_TABLE ;

Upvotes: 2

Vidya Sagar
Vidya Sagar

Reputation: 1719

To convert a timestamp to millis, you could use

datediff('ms', '1970-01-01', now());

If you want it for seconds, try dividing it by 1000.

Source

Upvotes: 3

ted
ted

Reputation: 14744

Try : 

SELECT parsedatetime(my_date, 'yyyy-MM-dd hh:mm:ss') FROM my_tabe

Upvotes: 0

Related Questions