How to convert date column value which is in CST timezone-CodePudding

For example column name Start_date is having value like below in hive which datatype is string

02-JUN-22 11.13.22 AM CST

I want to convert the value as below

2022-06-02

tried to_Date function but getting null values

CodePudding user response：

You can try below one,

And also if you have 'AM CST' values included as part of your input string, then you can trim or take substring value to match the below query.

select from_unixtime(unix_timestamp('02-JUN-22 11.13.22' ,'dd-MMM-yy'), 'yyyy-MM-dd');

CodePudding user response：

Use the built-in DateTime/TimeZone functionality

<?php
$mysqlDate = '2009-04-01 15:36:13';
$dateTime = new DateTime ($mysqlDate);
$dateTime->setTimezone(new DateTimeZone('America/Los_Angeles'));
?>

CodePudding user response：

have you tried to_char on your query?

for example:

select to_char(start_date,'yyyy-mm-dd') from your table;

CodePudding user response：

Convert to timestamp in Hive format taking into account the timezone (use from_unixtime(unix_timestamp(col, pattern))), see patterns, then use to_date.

Demo:

select to_date(from_unixtime(unix_timestamp('02-JUN-22 11.13.22 AM CST','dd-MMM-yy hh.mm.ss a z')))

Result:

2022-06-02