Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2012
    Posts
    9

    Unanswered: Greater than 24 hour check?

    What's the best way to check if a timestamp field is more than 24 hours old?

    For example, I know I can work out the time difference by using now()-updatetime (where updatetime is a timestamp field), but how do I run a greater than check in PSQL?

    I want to set the status of a ticket to closed, if the last update is over 24 hours old.

    Also, is using the following code a good way of doing it?

    Code:
    update x
    set status='closed'
    where (select statement to get difference in times) > 1 day
    Last edited by sqln00; 04-11-12 at 18:47.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It may be better to ask the question on DBMS product specific forum.
    Because, date/time calculations are quite different on each DBMS.

  3. #3
    Join Date
    Apr 2012
    Posts
    9
    It's PSQL if that helps any?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The following is ANSI SQL:
    Code:
    WHERE current_timestamp - updatetime > interval '1' day
    That will work in PostgreSQL (psql) just fine.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by sqln00 View Post
    ...

    I want to set the status of a ticket to closed, if the last update is over 24 hours old.

    Also, is using the following code a good way of doing it?

    Code:
    update x
    set status='closed'
    where (select statement to get difference in times) > 1 day
    Please supply CREATE TABLE statements and INSERT statements, and expected results.

    It looks like the table x has a column status.

    > if the last update ...
    how to know when updated?(what column in what table)
    "last" may be able to know by MAX(updated).

  6. #6
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by shammat View Post
    Code:
    WHERE current_timestamp - updatetime > interval '1' day
    Not to nitpick, but doesn't the entire interval need to be quoted? E.g.:
    Code:
    WHERE current_timestamp - updatetime > interval '1 day'

  7. #7
    Join Date
    Apr 2012
    Posts
    9
    Thank you, shammat!

    Problem is, now I get the following error:
    "ERROR: more than one row returned by a subquery used as an expression"

    Can anyone give me pointers to where I've gone wrong?

    I have two tables:

    ticket(id, problem, status, priority, loggedtime, customerID, productID)
    ticketupdate(id, message, updatetime, ticketid, staffid)

    (I think the field types are self-explanatory?) Status can be either open, or closed, and priority ranges from 1-3. ticketupdate.ticketid = ticket.id. loggedtime and updatetime are timestamps.

    The code I have at the moment is as follows. This code returns the timescale correctly, for every row, but when I wrap an update query around it, I get the error I mentioned above.

    Code:
    select distinct 
        now() - max(time.update) as timescale
    from
        ticket, ticketupdate
    
    inner join (
            select distinct
    		ticketupdate.id,
    		updatetime as update
            from ticketupdate, ticket
            WHERE ticket.id = ticketupdate.ticketid       
            group by ticketupdate.id, ticketupdate.updatetime
        ) as time
    using (id)
    WHERE ticket.id = ticketupdate.ticketid
    AND status='open'
    group by ticket.id, ticket.loggedtime, status
    And the code wrapped by the update query:

    Code:
    update ticket
    set status='closed'
    WHERE 
    (
    select distinct 
        now() - max(time.update) as timescale
    from
        ticket, ticketupdate
    
    inner join (
            select distinct
    		ticketupdate.id,
    		updatetime as update
            from ticketupdate, ticket
            WHERE ticket.id = ticketupdate.ticketid       
            group by ticketupdate.id, ticketupdate.updatetime
        ) as time
    using (id)
    WHERE ticket.id = ticketupdate.ticketid
    AND status='open'
    group by ticket.id, ticket.loggedtime, status
    ) > interval '1 day'
    Any help offered would be greatly appreciated! even if you're just pointing me in the right direction =)

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You are mixing implicit JOINS (tables listed in the where clause) and explicit joins in your SELECT statement.
    Although it's not the cause of your error, you should not do that. It's error prone and leads to confustion. Use the JOIN syntax for all tables:
    Code:
    select distinct 
        now() - max(time.update) as timescale
    from
        ticket
    inner join 
        ticketupdate on ticket.id = ticketupdate.ticketid
    inner join (
            select distinct
    		ticketupdate.id,
    		updatetime as update
            from ticketupdate, ticket
            WHERE ticket.id = ticketupdate.ticketid       
            group by ticketupdate.id, ticketupdate.updatetime
        ) as time
    using (id)
    WHERE ticket.status='open'
    group by ticket.id, ticket.loggedtime, ticket.status
    Code:
    update ticket
    set status='closed'
    WHERE 
    (
     ...
     ) > interval '1 day'
    Well, the error message is pretty self explanatory. Your subselect returns more than one row, but you are comparing that to a single value. You have to make your sub-select return exactly one row in order to be able to use =, > or < operators.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by futurity View Post
    Not to nitpick, but doesn't the entire interval need to be quoted? E.g.:
    Code:
    WHERE current_timestamp - updatetime > interval '1 day'
    No. At least not according to the ANSI standard.
    And PostgreSQL and Oracle do accept it the way I wrote it but at least PostgreSQL also accepts your "format"

  10. #10
    Join Date
    Apr 2012
    Posts
    9
    Quote Originally Posted by shammat View Post
    You are mixing implicit JOINS (tables listed in the where clause) and explicit joins in your SELECT statement.
    Although it's not the cause of your error, you should not do that. It's error prone and leads to confustion. Use the JOIN syntax for all tables

    Well, the error message is pretty self explanatory. Your subselect returns more than one row, but you are comparing that to a single value. You have to make your sub-select return exactly one row in order to be able to use =, > or < operators.
    Ah, thanks for pointing that out to me, Shammat!

    What would you recommend is the best way then to grab only one row? Or perform the operation on only one row? Since I want this to be a check which checks all of the rows when the query is run.

  11. #11
    Join Date
    May 2008
    Posts
    277
    You need to think a little bit about how you need to combine these queries.

    To start off with, you want to update rows in one table (ticket) based on rows in another table (ticketupdate). While we might normally use a JOIN for this, UPDATE queries don't really allow us to that, so instead we need to use a subquery.

    Now, what values are common between ticket and ticketupdate? Only one: the id number of the ticket. So that's our starting point:

    Code:
    update ticket
    set status = 'closed'
    where id in (select ticketid from ticketupdate ...)
    So which ticket ids do we want from ticketupdate? Those whose "last update is over 24 hours old." OK, so we're interested in comparing the last -- or MAX -- update time for a particular ticket to the current time. So we want something similar to:
    Code:
    WHERE current_timestamp - updatetime > interval '1 day'
    but only for the latest updatetime. There's an SQL construct that specifically allows us to do this; it's basically the same as WHERE but is used for aggregate functions (such as MAX, MIN, COUNT, etc).

    Hopefully this helps, but if you're having trouble getting your head wrapped around this, or are just starting to learn SQL, I'd recommend spending some time with Learn SQL The Hard Way. It's a work-in-progress, but it's done enough that I think it'll help you at least get your feet on the ground.

  12. #12
    Join Date
    Apr 2012
    Posts
    9
    AH! That helped perfectly, futurity. Following your advice I now have a query which is a hell of a lot shorter - and works! Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •