1

For Redshift

I have strings like "1:00 PM - 9:15 PM" in a column called workhours.

What's the most efficient way in SQL to convert this type of string in this column to two columns (start time, end time), resembling something like :

start_time | end_time
13:00         21:15

I know the first step is start with:

select
   split_part(workhours, '-', 1) as start_time,
   split_part(workhours, '-', 2) as end_time

but from here, what's the best way to turn the "1:00PM" into "13:00" and the "9:15PM" into "21:15"?

Edit: I would also like the solution to be able to automatically accommodate both AM and PM without me having to manually specific which (hope that makes sense).

Thanks in advance!

SpicyClubSauce
  • 4,076
  • 13
  • 37
  • 62
  • Not a formal answer, but is there any chance you can bring your date/time into the database in a better format? Databases are not well suited for this type of string manipulation work. – Tim Biegeleisen Mar 08 '18 at 04:54
  • unfortunately no, this is the hand that was dealt :/ – SpicyClubSauce Mar 08 '18 at 04:57
  • I would suggest to convert these string into time using your application code and then store value as per database format i.e. 13 , 21:15 as time data type column – Alpesh Jikadra Mar 08 '18 at 05:00

1 Answers1

3

The following solution builds a dummy timestamp using January 1st of the current year, along with the TO_TIMESTAMP function, which the latest Redshift should support. It then extracts out the 24 hour time portion using TO_CHAR.

SELECT
    TO_CHAR(TO_TIMESTAMP('2018-01-01 ' || SPLIT_PART('1:00 PM - 9:15 PM', '-', 1),
    'YYYY-MM-DD HH:MI PM'), 'HH24:MI:SS') AS part1,
    TO_CHAR(TO_TIMESTAMP('2018-01-01 ' || SPLIT_PART('1:00 PM - 9:15 PM', '-', 2),
    'YYYY-MM-DD HH:MI PM'), 'HH24:MI:SS') AS part2;

enter image description here

Demo

But I think the best long term solution is to just store date information as a date type in your database. Most likely, there is some notion of date associated with these times, and storing time by itself is probably not a use case which will come up often in real life.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Ah, sorry -- should've specified that this is Redshift. there is no datatype`time`, the closest is `timestamp` and casting something like "11:00 AM" to `timestamp` says invalid input syntax – SpicyClubSauce Mar 08 '18 at 05:12
  • @SpicyClubSauce I gave you an option which uses `TO_TIMESTAMP`. The latest version of Redshift [is supposed to support this](https://stackoverflow.com/questions/31636304/convert-text-to-timestamp-in-redshift). If it doesn't, then you are probably looking at a _very_ ugly query, in which case you might want to scrub your data before bringing it into the database. – Tim Biegeleisen Mar 08 '18 at 05:27
  • why is it necessary to add on the YYYY-MM-DD part? – SpicyClubSauce Mar 08 '18 at 05:30
  • Um...because it won't work, even on Postgres, if you don't do that. You need to pass in a _timestamp_ string (date + time), not just time. If it works for you on Redshift without doing this, then of course go with it. – Tim Biegeleisen Mar 08 '18 at 05:31