Clever Geek Handbook

H2. How to convert date in seconds to sql?

Is there a MySQL analogue of time_to_sec ()?

I listened to the query execution like in H2 database:

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


source to share


3 answers


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

To convert the timestamp in seconds from epoch, compile and add a Java class to the h2 classpath containing:

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

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



CREATE ALIAS TIME_SECS FOR "TimeFunc.getSeconds";

SELECT TIME_SECS(CURRENT_TIMESTAMP);

Outputs:

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


source


Instead of adding a function in H2, you can put the date on timsestamp and then use the formatdatetime behind http://www.h2database.com/html/functions.html . Alternatively, type in string and use parsedatetime. Examples of both:

-- 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 time zones can be found in the / usr / share / zoneinfo directory, and language codes are listed at http://en.wikipedia.org/wiki/ISO_639-1 - ISO 639 Standard Part 1.

+2


source


I think this is the simplest code.

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


source






More articles:


All Articles