• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

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;
0
szadroga
Asked:
szadroga
  • 2
2 Solutions
 
David KrollCommented:
DATEDIFF ( datepart , startdate , enddate )

http://msdn.microsoft.com/en-us/library/ms189794.aspx
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
szadrogaAuthor Commented:
my mistake, i didnt realize i was working with a postgresql db

i had to use date_part('day', ######::timestamp - ######::timestamp)
0
 
szadrogaAuthor Commented:
was using a different db type than i stated so i provided the solution i used
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now