UNIX_TIMESTAMP with milliseconds

  • Anonymous - 2012-12-03


       Working with Java Date or Joda DateTime requires dealing with milliseconds. I can save and load date in milliseconds using a BIGINT field, and this is ok.

       But when I need to work with such fields using SQL, there is a problem.

       If I use:          field = UNIX_TIMESTAMP(CURRENT_TIMESTAMP)

       I get (correctly) the seconds from 1970.

       Is there an option to get milliseconds, or in general, how can I do to set a field (BIGINT) to the current milliseconds from 1970, to be compliant with Java ? Of course, UNIX_TIMESTAMP(CURRENT_TIMESTAMP)*1000 is NOT a solutions, as precision is at second.

        Basically I need to obtain a BIGINT value of milliseconds of CURRENT_TIMESTAMP.


  • Fred Toussi

    Fred Toussi - 2012-12-03

    The UNIX_TIMESTAMP function is compatible with the MySQL function of the same name, and returns a bigint

    We may add a new function to return fractional values.

    In the meantime, write your own Java function to convert a timestamp to its millisecond value and use CREATE FUNCTION UNIX_MILLISECOND(TIMESTAMP TS) RETURNS BIGINT LANGUAGE JAVA … etc  EXTERNAL NAME 'your static java method fully qualified name'. See the Guide on SQL Invoked Routines

  • Anonymous - 2012-12-03

    Thanks fredt.

       Please add UNIX_MILLISECOND(TIMESTAMP TS), this is 110% useful to interact with Java Data milliseconds precision.

       In meantime, I've just defined this function to emulate UNIX_MILLISECOND(CURRENT_TIMESTAMP):

      EXTERNAL NAME 'CLASSPATH:java.lang.System.currentTimeMillis'

    Is this function solid as UNIX_MILLISECOND(CURRENT_TIMESTAMP) or not? Any problem I can get ?

  • Anonymous - 2012-12-03

    Basically I was not able to determine if I've to use DETERMINISTIC or NOT. I want to return the JVM currentTimeMillis as is, updated at function call time.

    I've to perform CREATE FUNCTION for every database running on a hsqldb server, or it is server-specific ? Sorry, but I was not able to find this info (i.e. how to list all custom defined functions, etc.. This should help a lot of people)

    Thanks again.

  • Fred Toussi

    Fred Toussi - 2012-12-03

    Your function is absolutely fine. It is NOT DETERMINISTIC because it will return different values if you call it several times.

    With CREATE FUNCTION, you get a function in the current schema of the current database. Therefore you have to define it for each database.

    Use a SELECT from INFORMATION_SCHEMA.ROUTINES view to list the user-defined routines.

  • Fred Toussi

    Fred Toussi - 2012-12-03

    It is easy to write the Java function with a param . It goes like

    public static long timeMillis(Timestamp ts) {
    return ts.getTime();

  • Anonymous - 2012-12-03

    Thank you for your feedback!

    Very useful.

    I hope UNIX_MILLISECOND will be added in next release as built-in function.



Log in to post a comment.