Solved

SQL code to determine the time between to timestamps

Posted on 2014-09-18
4
146 Views
Last Modified: 2014-09-23
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
Comment
Question by:szadroga
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
David Kroll earned 500 total points
ID: 40330377
DATEDIFF ( datepart , startdate , enddate )

http://msdn.microsoft.com/en-us/library/ms189794.aspx
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40330399
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
 

Assisted Solution

by:szadroga
szadroga earned 0 total points
ID: 40330443
my mistake, i didnt realize i was working with a postgresql db

i had to use date_part('day', ######::timestamp - ######::timestamp)
0
 

Author Closing Comment

by:szadroga
ID: 40338647
was using a different db type than i stated so i provided the solution i used
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question