Calculate time difference in minutes

redshifttime

I am Amazon Redshift. I tried to calculate the time difference between two time stamp columns in minutes, but the timestampdiff function does not work on it. The error is telling me that minute column does not exist in the table I query. At the same time some answers suggest that timestampdiff does not work for PostgreSQL, but function like date_part does not work on my end as well. Could anyone share experience or any document for the grammar and function on Amazon Redshift?

  select 
      email,
      createddate,
      lastloggedin,
      timestampdiff(minute, createddate, lastloggedin)
       from udb.user
       where createddate >= '2019-09-01' and createddate <= '2019-09-30'

Error: [Amazon](500310) Invalid operation: column "minute" does not exist in user; 
[SQL State=42703, DB Errorcode=500310]
1 statement failed.

Best Answer

Checking the docs for Amazon Redshift shows this:

DATEDIFF ( datepart, {date|time|timetz|timestamp}, {date|time|time|timestamp} )

datepart: The specific part of the date or time value (year, month, or day, hour, minute, second, millisecond, or microsecond) that the function operates on. For more information, see Date parts for date or timestamp functions.

Specifically, DATEDIFF determines the number of date part boundaries that are crossed between two expressions. For example, suppose that you're calculating the difference in years between two dates, 12-31-2008 and 01-01-2009. In this case, the function returns 1 year despite the fact that these dates are only one day apart. If you are finding the difference in hours between two timestamps, 01-01-2009 8:30:00 and 01-01-2009 10:00:00, the result is 2 hours. If you are finding the difference in hours between two timestamps, 8:30:00 and 10:00:00, the result is 2 hours.

date|time|timetz|timestamp: A DATE, TIME, TIMETZ, or TIMESTAMP column or expressions that implicitly convert to a DATE, TIME, TIMETZ, or TIMESTAMP. The expressions must both contain the specified date or time part. If the second date or time is later than the first date or time, the result is positive. If the second date or time is earlier than the first date or time, the result is negative.

So, your query should be:

select 
    email,
    createddate,
    lastloggedin,
    datediff(minute, createddate, lastloggedin)
from udb.user
where createddate >= '2019-09-01' and createddate <= '2019-09-30'

Related Solutions

Oracle – Calculating Time Elapsed

Try this. Logically it's a self-join.

WITH transaction_user_parts AS
(
   select h.id, h.trtype, T.DESCRIPTION, hist.internal_status,
   ts.description status_Desc, h.val_dte, h.account_dte, 
   hist.signature_timestamp, usr.login_name as user_name
    from k$transaction_header h, k$transtypes t, k$transaction_history hist, k$usr usr, K$TRANSACTION_STATUS ts
   where h.trtype = t.trtype
   and hist.trans_id = h.id
   and usr.id=hist.signature_usr
   and ts.id=hist.internal_status
   AND H.ACCOUNT_DTE = TO_DATE(&DATE_YYYYMMDD, 'yyyymmdd')
)
SELECT  t1.id
      , t1.internal_status || ' -> ' || t2.internal_status internal_status_change
      , t2.user_name
      , (t2.signature_timestamp - t1.signature_timestamp) time_elapsed
FROM transaction_user_parts t1
JOIN transaction_user_parts t2
  ON (t1.id = t2.id
       AND t1.signature_timestamp <= t2.signature_timestamp
       AND (
             (t1.internal_status='0' AND t2.internal_status='1')
            OR
             (t1.internal_status='1' AND t2.internal_status='B2')
            OR
             (t1.internal_status='0' AND t2.internal_status='B3')
           )
      );

Sql-server – Getting Time Duration

You don't say what DB you are using, but that syntax looks like MS SQL Server.

Also you do not properly state the table structure - the function refers to VehicleID which doesn't appear in you sample data and you don't say what the data types or what indexes and keys are present. All of this could be significant.

You are pulling a bunch if data into a table variable and processing it there which could be a problem - if there are many rows per vehicle this will hit disk and cause slowness due to that IO.

With SQL Server 2012 or later this is quite easy to do using window functions:

SELECT ID, DATEDIFF(MINUTE
            , TrackTime
            , LEAD(TimeTrack) OVER (ORDER BY TimeTrack)
            )
FROM Tracking
WHERE VehicleID = @VehicleID
AND TrackTime BETWEEN @starttime AND @endtime
AND Speed > @speedlimit
ORDER BY TimeTrack

For this to be efficient you'll at very least need an index covering VehicleID. Better still either one covering VehicleID & TrackTime (perhaps including speed & ID), or have VehicleID & TrackTime be your clustered index (though for a definite recommendation we need to know a lot more about the other queries the table sees: picking your clustered index to optimise this query might show others).

I'd your indexes are right this should pass over the data once, with and index seek rather than a scan (or worse, a table scan) and no spooling to disk unless the data per vehicle is really massive.

If you want results for multiple vehicles then you need to tell the lead function to split by VehicleID so it becomes LEAD(TimeTrack) OVER (PARTITION BY VehicleID ORDER BY TimeTrack), and add VehicleID to the main ordering clause too.

Best Answer

Related Solutions

Oracle – Calculating Time Elapsed

Sql-server – Getting Time Duration

Related Question