Question 95675 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Sum varchar time to a timestamp in Redshift – Amazon web services

I am trying to sum a timestamp with a time that I have to cast from varchar to time. This is my attempt:

select 
    my_timestamp + cast(my_str_time as time) as my_end_time
from my_db.my_table;

my_timestamp format (already a timestamp):

2022-07-16 02:51:11
2022-07-16 03:18:06
...

my_str_time format (which is a varchar of 50 in the HH:mm:ss format):

00:03:51 
00:04:13
...

The error I’m getting:

specified types or functions (one per info message) not supported on redshift tables

Is there any way to calculate this or I would have to alter that varchar column to time?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This is what worked for me (very inefficient):

    with my_times as (
    select
        my_timestamp as my_start_time,
        my_time,
        SUBSTRING(my_time, 0, 3)::integer as my_time_hour,
        SUBSTRING(my_time, 4, 2)::integer as my_time_min,
        SUBSTRING(my_time, 7, 2)::integer as my_time_sec
    from my_db.my_table
)
select
    my_start_time,
    my_time,
    dateadd(HOUR, my_time_hour, dateadd(MINUTE, my_time_min, dateadd(SECOND, my_time_sec, my_start_time))) as my_end_time
from my_times;

    Or even better as Bill suggested:

    select
    my_timestamp,
    my_time,
    my_timestamp + cast(my_time as interval) as my_end_time
from my_db.my_table
limit 5;

  2. 0

    You can try to use a combination of to_timestamp and extract (not too pretty but it should work):

    extract(sec from to_timestamp(my_str_time,'hh:mi:ss')),
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search