Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL code to determine the time between to timestamps

Posted on 2014-09-18
4
148 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 48

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Find Parent/Child relationship tree between numeric range. 7 47
Access 2003 query lost it's only join 7 32
query question 12 32
SQL query and VBA 5 46
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

860 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