CURRENT_TIMESTAMP in milliseconds in h2

Question

The question CURRENT_TIMESTAMP in milliseconds discussed how to "get milliseconds out of a timestamp in MySql or PostgreSql". But the methods in the answers don't work because H2 doesn't support MySQL methods like conv.

I want to use a variant of CURRENT_TIMESTAMP for the defaultValueComputed of my schema file.

How can I get the exact milliseconds of a timestamp in H2? I want it be a long. I also want the milliseconds returned to be in Unix time.

Here it is in my schema file:

<column name="create_time" type="long" defaultValueComputed="?"
    <constraints nullable="false"/>
</column>

I have this DAO object:

public interface MyDao extends Transactional<MyDao> {
    @SqlUpdate(
        "INSERT INTO my_table "(id, create_time)" +
        "VALUES (:id, :create_time)"
    void insert(@BindBean MyObject myObject);
}

I looked at the "Time and Date Functions" section of http://www.h2database.com/html/functions.html and couldn't find anything that could do this though maybe PARSEDATETIME somehow could work.

EDIT: Maybe an alias is possible. See groups.google.com/forum/#!topic/h2-database/kziTTTNlB9o:

I'm porting an application to H2 that was initially written for MySQL (the queries contain some MySQL-specific syntax). Probably the most challenging part of getting the queries running on H2 has been the use of the MySQL date functions like DATE(), UNIX_TIMESTAMP(), and FROM_UNIXTIME(). The good news is: because H2 supports creating aliases for these, I was able to write a very small Java class that implements these MySQL functions for H2, and once I aliased them into place, H2 runs just about all of our queries without modification.

Answer 1

I've tried the other answer here and it wasn't accurate - there was a small diff between the result and the actual Unix timestamp.

This, however, seems to work:

SELECT EXTRACT (EPOCH from CURRENT_TIMESTAMP()) * 1000

It seems like 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.

Answer 2

I know this is old question but anyone like me, can be search answer for this question. We can get current time by millisecond with h2 DATEDIFF() function.

Fisrtly we pass current_timestamp and first date value and return type SECOND as a parameters like : DATEDIFF('SECOND', DATE '1970-01-01', CURRENT_TIMESTAMP()) * 1000 the returned result is current_time's millisecond for us.