Dave Tolls wrote:What does the book.ticket table look like and what are you actually trying to compare?
At the moment it looks like you are selecting all the time values for tickets marked as planning, and all the time values for tickets marked as closed.
So what defines which planning time is subtracted from which closing time?
Tim Holloway wrote:I don't understand it either. Partly because it's incomplete and we can only see how you're trying to do it and not what you actually what you want to really do. Partly because I'm confused about the need for all those transformations on dates and whether they are even necessary.
One thing to keep in mind, though. Timestamp-style "date" fields in most databases are imprecise. Unless it's a true date (with no time) field or you have an integral number of seconds, treat the date/time values with the same care you would treat floating-point numbers.
This fuzziness is also why date/time columns are usually not safe to use as primary keys.
Tim Holloway wrote:For myself, I'd just SELECT date_part('day", final_date - initial_date) AS day_diff WHERE day_diff > $too_long as a rough computation, and if you're worried about hour/minute/seconds differences resulting in a day to short, add an interval of 23h 59m 59.99s or so to the date difference calculation. And not need all that fancy stuff.
PostgreSQL has a wealth of date/time functions, so there are lots of options to play with.
Tim Holloway wrote:The thing that doesn't make sense here is precisely because your table doesn't appear to have initial_date and final_date columns. Instead you're using the same column value (t.time) for both dates.