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

Is there analog og the MySQL's time_to_sec() ?

I heed to perform query like the following on H2 database:

select * from order
join timmingSettings on order.timmingSettings = timmingSetti开发者_C百科ngs.id
where (order.time-timmingSettings.timeout) < current_timestamp

---

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)

---

In lieu of adding a function to H2, you can cast the date to a timsestamp and then use formatdatetime per http://www.h2database.com/html/functions.html. Alternatively, cast to string and use parsedatetime. Examples of both follow:

-- to convert using parsedatetime, done_on stores 
select parsedatetime(done_on, 'ssss', 'en', 'Europe/Dublin');
-- for this example, assume done_on stores a timestamp
-- to convert using formatdatetime
select formatdatetime(done_on, 'ssss', 'en', 'Europe/Dublin');

Valid timezones can be found in your /usr/share/zoneinfo directory and language codes are per the list at http://en.wikipedia.org/wiki/ISO_639-1 -- the ISO 639 part 1 standard.

---

I think this is the most simple code.

select DATEDIFF('second',timestamp '1970-01-01 00:00:00' ,  CURRENT_TIMESTAMP())

---

SELECT EXTRACT (EPOCH FROM <value>)

So

select * from order join timmingSettings on order.timmingSettings = timmingSettings.id where (extract(epoch from order.time)-extract(epoch from timmingSettings.timeout)) < extract(epoch from current_timestamp)