Redshift - Issue displaying time difference in table stored in table

Question

I am trying to find the time difference between two time stamps and store it in a column. When I check the output in the table, I see the value to be a huge number and not the difference in day/hours. I am using amazon redshift as the database.

Data_type of time_duration column : varchar

Given below is the sample:

order_no,order_date,complain_date,time_duration
1001,2018-03-10 04:00:00,2018-03-11 07:00:00,97200000000

But I am expecting time_duration column to show 1 day,3 hours

This issue happens when I store the time_duration in a table and then query to view the output.

Could anyone assist.

Thanks.

Answer 1

Do it following way, it will give hours difference select datediff(hour,order_date,complain_date) as diff_in_hour from your_table ;

If you want to do it day, do it following way select datediff(day,order_date,complain_date) as diff_in_day from your_table;

You could use datediff function to update your table column time_duration.

Refer Redshift documentation for more details.