Solved

SQL code to determine the time between to timestamps

Posted on 2014-09-18
4
149 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 49

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to double quote a string for an inline sql statement. 8 95
SQL Syntax 14 41
SQL query and VBA 5 55
TSQL Assignining CTE column in to a variable 3 13
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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