Search code examples
sqltimezonevertica

Vertica String Timezone conversion

I have a list of 'HH24:MI:SS' stored as a string and I need to factor in the timezone. Is there a way to select a.hour at timezone from database with how it's currently stored?

11:30:00
11:00:00
12:00:00

not sure if there's a way or not. I've tried converting to a date or timestamp but no luck since it's stored as a string. I feel like theres a simple way but I'm not seeing it

Solution

  • Do you mean this?

    WITH
indata(s) AS (
          SELECT '11:30:00'
UNION ALL SELECT '11:00:00'
UNION ALL SELECT '12:00:00'
)
SELECT 
  s::TIME                                      AS the_time
, s::TIME AT TIMEZONE 'America/New_York'       AS the_other_time
, HOUR(s::TIME)                                AS the_hour
, HOUR(s::TIME AT TIMEZONE 'America/New_York') AS the_other_time
FROM indata;
-- out  the_time | the_other_time | the_hour | the_other_time 
-- out ----------+----------------+----------+----------------
-- out  11:30:00 | 05:30:00-05    |       11 |              5
-- out  11:00:00 | 05:00:00-05    |       11 |              5
-- out  12:00:00 | 06:00:00-05    |       12 |              6