Solved

SQL code to determine the time between to timestamps

Posted on 2014-09-18
4
145 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 46

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now