Convert Date TImestamp In Presto-CodePudding

is there any way to convert from 2022-06-15 10:21:05.698000000 to this 2022-06-15 10:21:05 format?

I have data in hive (Datatype is string) which contains data like this 2022-06-15 10:21:05.698000000. I need to insert this data in oracle, in oracle data type is date. I am using below query while selecting the data from hive.

select hive_date,cast(coalesce(substr(A.hive_date, 1,19),substr(A.hive_date2,1,19)) as timestamp) 
as oracle_date from test A limit 10;

It's showing below output.

hive_date                                                  oracle_date
2022-06-15 10:21:05.698000000          |                   2022-06-15 10:21:05.000

I want to convert this till seconds 2022-06-15 10:21:05 so i can insert into it in oracle. Can someone plz suggest me.

CodePudding user response：

You can use date functions - date_parse and date_format:

select date_format(date_parse('2022-06-15 10:21:05.698000000', '%Y-%m-%d %H:%i:%s.%f'), '%Y-%m-%d %H:%i:%s')

Output:

_col0
2022-06-15 10:21:05

Also I would suggest using coalesce before manipulations i.e. in original attempt coalesce(substr(A.hive_date, 1,19),substr(A.hive_date2,1,19)) -> substr(coalesce(A.hive_date, A.hive_date2), 1, 19)

Also possibly you need to just use trim on the data, like:

substr(trim(coalesce(A.hive_date, A.hive_date2)), 1, 19)

Or:

select date_format(
        date_parse(
            trim(coalesce(A.hive_date, A.hive_date2)),
            '%Y-%m-%d %H:%i:%s.%f'
        ),
        '%Y-%m-%d %H:%i:%s'
    )

CodePudding user response：

It's possible that all you need is:

SELECT cast('2022-06-15 10:21:05.698000000' AS timestamp(0));

_col0
2022-06-15 10:21:06

But, if it needs to be a string, this truncates:

SELECT date_format(timestamp '2022-06-15 10:21:05.698000000', '%Y-%m-%d %H:%i:%s');

_col0
2022-06-15 10:21:05

Or this method rounds:

SELECT cast(cast('2022-06-15 10:21:05.698000000' AS timestamp(0)) AS varchar);

_col0
2022-06-15 10:21:06