I looked through online solutions for this, none worked for me so I'm posting here. I have separate date and time columns, both stored as integers. I am able to convert the date column to DATE, but not for the Time column. The column value is: 52700
, when using TO_TIME(TO_CHAR(OHCRTM))
I get 14:38:20
but it should be 07:27:00
. I've tried various formatting (TO_TIME(TO_CHAR(OHCRTM),'HH24.MI.SS'
) but I get a 'cannot parse' error. Any idea how I can get the correct time?
CodePudding user response:
14:38:20 is the right answer, unless you can give us the logic that would make it 07:27:00.
52700 seconds are exactly 14 hours, 38 minutes, and 20 seconds.
Another option to read 52700 would be a time without the colons, ie 05:27:00. To parse it like that the needed SQL is:
select to_time(52700::string, 'HHMISS');
I guess 05 becomes 07 after a timezone conversion then?