asked on

SQL code to determine the time between to timestamps

I have the following query which is working fine, but i need to determine the "time" between the min_event_timestamp and max_event_timestamp as a new field.

SELECT
events.event_log.user_id,
events.event.name AS event_name,
MIN(events.event_log.timestamp) As min_event_timestamp,
MAX(events.event_log.timestamp) AS max_event_timestamp,

FROM
events.event
INNER JOIN
events.event_log
ON
(
events.event.id = events.event_log.event_id)
WHERE
events.event_log.event_id IN (4)

GROUP BY event_log.user_id, event.name;

ASKER CERTIFIED SOLUTION

David Kroll

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

A new field means an output column only?
If so, try this (considering that you want the result in seconds (s), otherwise you can alter the datepart value in DATEDIFF function:

WITH MyCTE (user_id, event_name, min_event_timestamp, max_event_timestamp)
AS (
SELECT
     events.event_log.user_id,
     events.event.name          AS event_name,
     MIN(events.event_log.timestamp) As min_event_timestamp,
     MAX(events.event_log.timestamp) AS max_event_timestamp,
     
 FROM
     events.event
 INNER JOIN
     events.event_log
 ON
     (
         events.event.id = events.event_log.event_id)
 WHERE
     events.event_log.event_id IN (4)
 GROUP BY event_log.user_id, event.name)

SELECT user_id, event_name, min_event_timestamp, max_event_timestamp, DATEDIFF (s, min_event_timestamp, max_event_timestamp) 
FROM MyCTE

Open in new window

SOLUTION

szadroga

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

szadroga

ASKER

was using a different db type than i stated so i provided the solution i used