Take the 2-minute tour ×

I am using derby and hsqldb for unit testing my code. In my code, i am using HQL (hibernate query language). In those queries, i am using from_unixtime(timestamp) to extract datetime from timestamp. It seems that derby anf hsqldb don't support from_unixtime. Also, seems to me that from_unixtime is mysql specific function. How correct is that? How can i test my hql query (which contains from_unixtime(timestamp) function using derby or hsqldb?

share|improve this question

2 Answers 2

FROM_UNIXTIME is indeed specific to MySQL.

HSQLDB 2.2.6 and later supports this function to convert a unix time (in seconds) to a datetime value:

TIMESTAMP( <second value> )

For example:

call timestamp( 1000000000) returns 2001-09-09 01:46:40.0

HSQLDB 2.2.6 also supports a MySQL syntax compatibility mode, which includes several automatic conversions for SQL statements written for MySQL.

share|improve this answer

In Derby, you may be able to use the JDBC escape function TIMESTAMPADD to add your interval in seconds to the base timestamp of January 1, 1970. Here's some docs: http://db.apache.org/derby/docs/10.8/ref/rrefjdbc88908.html Here's some discussion of translating back and forth between the MySQL techniques and the Derby techniques: http://objectmix.com/apache/646986-mysql-to_days-emulation-derby.html

share|improve this answer

Your Answer


