Sum ( Extract ( ) ) using RedShift
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
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.