Sum ( Extract ( ) ) using RedShift

Greg_B
Greg_B Member
in Magic ETL

Using RedShift,,, i know that EXTRACT ( minute from date1) will give me the minute from date1 (18:03.94 returns 3)

 

Ok,,, so now in my RedShift i said 

SELECT

SUM(   (EXTRACT(hour from date1-date2)*60) + extract(minute from date1-date2) ) 

 

So in my POSTGRES db, that returns everything great in total minutes..  Here's what i am seeing in redshift (hopefully i am coding it wrong).

 

Redshift gives me the full number of minutes between date1 and date2 from the extract(minute from date1 - date2).  So instead of < 60 being the result, it is the actual number of minutes between the two dates.  Which is what i was trying to get by adding in the extract(hour) * 60.

 

So now over in POSTGresql, using EXTRACT() i have to keep the HOUR*60, but the RedShift manipulates the EXTRACT( minute ) differently.  Has anyone else seen this before? 

 

SUM((extract(hour from end_dt - start_dt)*60) + extract(minute from end_dt - start_dt)) DURATION,  In POSTGRESql this is accurate

however that pasted into RedShift returns accurante+(hour*60).  So if the results was 188 minutes, the DB returns 188, however redshift returns 180 + 188 (3hours *60 + 188 minutes = 368).  This was CRAZY and i hope someone can test this on their system and validate my results, as this will need to be something handled whenever we create dataflows.  

 

Thanks, and sorry if I wrote this horribly. 

Best Answer

  • jhl
    jhl Member
    Answer ✓

    Hi,

     

    if what I think you want to do is what you want to do (calculate number of minutes between two timestamps) then I do not entirely understand why you cant just delete the first *60 part to get the right result? You definitely cant use the same code for this. Two different query languages return different results when using the same code, that is in itself not surprising.

     

    When you load data into Domo, the language that would have been used to query or manipulate the data in the original source no longer matters. If you load an excel file and start querying it in Domo using SQL, writing code in VBA will not give you anything except for error messages.

     

    I am not working with Postgre, but for Redshift you could just try using DATEDIFF  to calculate the minute difference directly. Also shorter ?

     

    Keep in mind that the order of the dates in DATEDIFF matters, so you might want to use ABS() or make sure it is set in a way that it never returns negative numbers.

     

    HTH JHL

Answers

  • jhl
    jhl Member
    Answer ✓

    Hi,

     

    if what I think you want to do is what you want to do (calculate number of minutes between two timestamps) then I do not entirely understand why you cant just delete the first *60 part to get the right result? You definitely cant use the same code for this. Two different query languages return different results when using the same code, that is in itself not surprising.

     

    When you load data into Domo, the language that would have been used to query or manipulate the data in the original source no longer matters. If you load an excel file and start querying it in Domo using SQL, writing code in VBA will not give you anything except for error messages.

     

    I am not working with Postgre, but for Redshift you could just try using DATEDIFF  to calculate the minute difference directly. Also shorter ?

     

    Keep in mind that the order of the dates in DATEDIFF matters, so you might want to use ABS() or make sure it is set in a way that it never returns negative numbers.

     

    HTH JHL

Categories