H2 Database. How to Convert Date to Seconds in SQL

H2 database. How to convert date to seconds in sql?

No, but it seems quite easy to add function to h2 if needed.

To convert a timestamp to seconds since epoch, compile and add a Java class to h2's classpath containing:

public class TimeFunc
{
  public static long getSeconds(java.sql.Timestamp ts)
  {
    return ts.getTime() / 1000;
  }
}

The function in the Java code can then be linked in h2 using CREATE ALIAS:

CREATE ALIAS TIME_SECS FOR "TimeFunc.getSeconds";

SELECT TIME_SECS(CURRENT_TIMESTAMP);

Produces:

TIME_SECS(CURRENT_TIMESTAMP())  
1255862217
(1 row, 0 ms)

Convert date "1472120311221" to datetime format in H2 Database?

Your timestamp seems to be in milliseconds, not in seconds.

Please use the following snippet:

select DATEADD('SECOND', 1472120311221/1000, DATE '1970-01-01');
-- 2016-08-25 11:18:31.0

Once adapted according to your exact needs, this becomes:

select FORMATDATETIME(
  DATEADD('SECOND', 1472120311221/1000, DATE '1970-01-01'),
  'YYYY-dd-MM HH:mm:ss'
);
-- 2016-25-08 11:18:31

write a query in h2 database to convert date format

Use PARSEDATETIME for example:

create table DATE_EXAMPLE (
    EXAMPLE DATE
);
insert into DATE_EXAMPLE values ('2020-02-03');
select * from DATE_EXAMPLE where EXAMPLE = PARSEDATETIME('03-february-20','dd-MMMM-yy');

H2 database string to timestamp

According to my test, with H2 version 1.3.170, the milliseconds are not actually zero, but 069:

select * from test;
ID  DATE  
1   2012-09-17 18:47:52.069

The same happens if you run:

call parsedatetime('17-09-2012 18:47:52.69', 'dd-MM-yyyy hh:mm:ss.SS');

If you add a zero then it works:

call parsedatetime('17-09-2012 18:47:52.690', 'dd-MM-yyyy hh:mm:ss.SS');

H2 internally uses java.text.SimpleDateFormat, so it has to live with the same limitations. If you find a solution within SimpleDateFormat, you can use it within the parsedatetime function in H2.

An alternative is to use the ISO timestamp format as defined in JDBC. This is supposed to work with all databases that conform the JDBC standard:

INSERT INTO TEST VALUES(2, {ts '2012-09-17 18:47:52.69'});

convert column data varchar to timestamp in H2 Database

Unfortunately, your timestamp is not in SQL or ISO format, so you can't simply change data type of a column. Automatic conversion is not possible in your case.

In 1.4.200 and older versions you need to perform two steps:

CREATE TABLE TEST(V VARCHAR);
INSERT INTO TEST VALUES ('7-18-2020 7:51');

UPDATE TEST SET V = PARSEDATETIME(V, 'M-d-yyyy HH:mm');
ALTER TABLE TEST ALTER COLUMN V SET DATA TYPE TIMESTAMP;

PARSEDATETIME returns a TIMESTAMP value, but here it is assigned back to VARCHAR column, this operation cause an implicit cast using SQL format ('2020-07-18 07:51:00') that can be converted back to TIMESTAMP automatically during the following change of data type of the column.

In H2 2.0.202 and newer versions there is a simpler one-step solution:

CREATE TABLE TEST(V VARCHAR);
INSERT INTO TEST VALUES ('7-18-2020 7:51');

ALTER TABLE TEST ALTER COLUMN V
    SET DATA TYPE TIMESTAMP USING PARSEDATETIME(V, 'M-d-yyyy HH:mm');

Calculate difference in seconds between two dates in various SQL databases (MySQL, Oracle, H2)

I used 60 + extract(SECOND FROM (b.created_ts)) - extract(SECOND FROM a.created_ts) and it works for all those databases.

---

Related Topics