Convert days into hours in Amazon redshift

Question

I want to convert a column with mixed formats like "1 day 07:00:00" and "2 days" into hours.

Here's a query that should work in Amazon Redshift:

SELECT
    CASE
        WHEN POSITION('day', estimated_time_to_pickup) > 0 THEN
            24 * CAST(SPLIT_PART(estimated_time_to_pickup, ' ', 1) AS INTEGER) +
            EXTRACT(HOUR FROM TO_TIMESTAMP(SUBSTRING(estimated_time_to_pickup, POSITION('day', estimated_time_to_pickup) + 3), 'HH24:MI:SS'))
        ELSE
            24 * CAST(TRIM(' days' FROM estimated_time_to_pickup)::INTEGER)
    END AS estimated_time_in_hours
FROM 
    "dev"."staging"."stg_zidship__service_levels_servicelevel"
LIMIT 100;

Answer 1

You can use the CASE WHEN clause along with regular expressions to extract the relevant information. Here's an example query:

SELECT
  your_column_with_days,
  CASE
    WHEN your_column_with_days ~ '^\d+ day[s]?$' THEN
      CAST(SUBSTRING(your_column_with_days FROM '(\d+) day[s]?$') AS INTEGER) * 24
    WHEN your_column_with_days ~ '^\d+ day[s]? (\d{2}):(\d{2}):(\d{2})$' THEN
      CAST(SUBSTRING(your_column_with_days FROM '(\d+) day[s]? (\d{2}):(\d{2}):(\d{2})$') AS INTEGER) * 24
      + CAST(EXTRACT(HOUR FROM INTERVAL '00:' || SUBSTRING(your_column_with_days FROM '(\d+) day[s]? (\d{2}):(\d{2}):(\d{2})$') || ' hours') AS INTEGER)
    ELSE
      NULL -- Handle other cases as needed
  END AS hours
FROM
  your_table;

Replace your_column_with_days with the actual name of the column in your table that stores the time in days, and your_table with the actual name of your table.

This query checks two patterns using regular expressions. The first pattern is for cases like '2 days', and the second pattern is for cases like '1 day 07:00:00'. The CASE WHEN clause then calculates the corresponding hours based on the matched pattern.