Datetime conversion

CrateDB
1

Hello everyone, Hope all are doing good.

Currently we using cratedb version 4.2.6.
I want to convert EPOC time to readable date format.
But column is in array , so when converting array it giving error.

Query is :-

select orderdata['lrDateTime'] from ap_db_crate.tripdetails_tripcreateddate limit 100;

want to convert lrDateTime column from epoc to date.

conversion query is

select date_format('%Y-%m-%dT%H:%i:%s.%fZ' ,orderdata['lrDateTime']) 
from ap_db_crate.tripdetails_tripcreateddate limit 100;

error is

select date_format('%Y-%m-%dT%H:%i:%s.%fZ' ,orderdata['lrDateTime']) 
from ap_db_crate.tripdetails_tripcreateddate limit 100;

create_orderdetails
create_orderdetails860×590 47.4 KB

error_conversion
error_conversion724×570 59.6 KB

For more details is attach the query with error.
Thanks
Vinayak Katkar

2

where lrDateTime is bigInt

3

have you tried casting it explicitly to timestamp ?

e.g.

SELECT orderdata['lrDateTime']::timestamptz

the error seems strange and actually implies, that it is an array(bigint) and not a bigint

4

If the timestamp is really on an array and you always need the first value you could use

date_format('%Y-%m-%dT%H:%i:%s.%fZ',orderdata['lrDateTime'][1])
5

Hi,
While execute above got below error.

SELECT orderdata[‘lrDateTime’]::timestamptz :-
SQLActionException[SQLParseException: Cannot cast expressions from type bigint_array to type timestamp with time zone]

date_format(‘%Y-%m-%dT%H:%i:%s.%fZ’,orderdata[‘lrDateTime’][1]) :-
SQLActionException[UnsupportedFeatureException: Nested array access is not supported]

6

After using below query
SELECT cast (unnest (orderdata[‘lrDateTime’])as timestamp)

output is came as per requirement.

Thanks for all your support.

1 Like