H2 database. How to convert date to seconds in sql?
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)
Convert date "1472120311221" to datetime format in H2 Database?
Your timestamp seems to be in milliseconds, not in seconds.
Please use the following snippet:
select DATEADD('SECOND', 1472120311221/1000, DATE '1970-01-01');
-- 2016-08-25 11:18:31.0
Once adapted according to your exact needs, this becomes:
select FORMATDATETIME(
DATEADD('SECOND', 1472120311221/1000, DATE '1970-01-01'),
'YYYY-dd-MM HH:mm:ss'
);
-- 2016-25-08 11:18:31
write a query in h2 database to convert date format
Use PARSEDATETIME for example:
create table DATE_EXAMPLE (
EXAMPLE DATE
);
insert into DATE_EXAMPLE values ('2020-02-03');
select * from DATE_EXAMPLE where EXAMPLE = PARSEDATETIME('03-february-20','dd-MMMM-yy');
H2 database string to timestamp
According to my test, with H2 version 1.3.170, the milliseconds are not actually zero, but 069:
select * from test;
ID DATE
1 2012-09-17 18:47:52.069
The same happens if you run:
call parsedatetime('17-09-2012 18:47:52.69', 'dd-MM-yyyy hh:mm:ss.SS');
If you add a zero then it works:
call parsedatetime('17-09-2012 18:47:52.690', 'dd-MM-yyyy hh:mm:ss.SS');
H2 internally uses java.text.SimpleDateFormat
, so it has to live with the same limitations. If you find a solution within SimpleDateFormat
, you can use it within the parsedatetime
function in H2.
An alternative is to use the ISO timestamp format as defined in JDBC. This is supposed to work with all databases that conform the JDBC standard:
INSERT INTO TEST VALUES(2, {ts '2012-09-17 18:47:52.69'});
convert column data varchar to timestamp in H2 Database
Unfortunately, your timestamp is not in SQL or ISO format, so you can't simply change data type of a column. Automatic conversion is not possible in your case.
In 1.4.200 and older versions you need to perform two steps:
CREATE TABLE TEST(V VARCHAR);
INSERT INTO TEST VALUES ('7-18-2020 7:51');
UPDATE TEST SET V = PARSEDATETIME(V, 'M-d-yyyy HH:mm');
ALTER TABLE TEST ALTER COLUMN V SET DATA TYPE TIMESTAMP;
PARSEDATETIME
returns a TIMESTAMP
value, but here it is assigned back to VARCHAR
column, this operation cause an implicit cast using SQL format ('2020-07-18 07:51:00'
) that can be converted back to TIMESTAMP
automatically during the following change of data type of the column.
In H2 2.0.202 and newer versions there is a simpler one-step solution:
CREATE TABLE TEST(V VARCHAR);
INSERT INTO TEST VALUES ('7-18-2020 7:51');
ALTER TABLE TEST ALTER COLUMN V
SET DATA TYPE TIMESTAMP USING PARSEDATETIME(V, 'M-d-yyyy HH:mm');
Calculate difference in seconds between two dates in various SQL databases (MySQL, Oracle, H2)
I used 60 + extract(SECOND FROM (b.created_ts)) - extract(SECOND FROM a.created_ts)
and it works for all those databases.
Related Topics
Select Count of Total Products as Well as Out of Stock Products from Table
Spark - Query Dataframe Based on Values from a Column in Another Dataframe
How to Find What Privileges a Group Has in Redshift
Fill a Field With the Values from Another Field in Ms Access
How to Divide Sum in Number from Count in Having
Sql String: Counting Words Inside a String
Postgresql Error: Syntax Error At or Near "Varchar"
How to Convert a Timestamp (Date Format) to Bigint in SQL
How to Display the Value of Avg Function Till Only Two Decimal Places in SQL
How to Select Only 1 Row from Oracle SQL
Get the Number of Digits After the Decimal Point of a Float (With or Without Decimal Part)
Insert If Not Exists Else Update
Select and Compare Two Datetime Columns from Different Table Without Having Any Relation
Row Numbers in Query Result Using Microsoft Access
Mysql Select Rows on First Occurrence of Each Unique Value
How to View SQL Table Structure in Oracle SQL Developer
Ssis Date in Derived Column as Yyyy-Mm-Dd Format
Sqlstate[Hy000] [2002] Php_Network_Getaddresses: Getaddrinfo Failed: Name or Service Not Known