Win a copy of Spring in Action (5th edition) this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Difference between two dates  RSS feed

 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello. I'm trying to calculate the difference between two dates on postgree. First i get the dates:




And then i would like to do something like that to calculate the difference between the recovered dates previously:




But i don't know how to put this last part (that one that calculates) together with the others queries. I tried with JOIN, UNION... But i get syntax error or the result is not what i want. Can someone help me?
 
Rancher
Posts: 3746
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Bartender
Posts: 19972
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
thomas topázio
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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?



Hello, Dave. You're right, i'm trying to select all the time values for tickets marked as planning and all the time values for tickets marked as closed. Let me explain my propose: I wanna calculate how long (in days) a task on my book table tooks between planning and closed. So first i get the date when the task is with status "planning" and them i get the date when changed to "closed" and try to calculate the diff. For the line bellow:



Is returned the follows error message:

ERROR:  column "final_date" does not exist
LINE 21:    DATE_PART('day', final_date::timestamp -  initial_date...
                            ^
HINT:  There is a column named " final_date" in table " final_date", but it cannot be referenced from this part of the query.

I guess thsi error is because i'm putting the alias on the diff and is not accepted but i don't know how to make this query work
 
thomas topázio
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.



Hi, Tim. Sorry if i wasn't clear. As i answered for Dave: My propose is to calculate how long (in days) a task on my book table tooks between planning and closed. So first i get the date when the task is with status "planning" and them i get the date when changed to "closed" and try to calculate the diff. About the Timestamp-style "date" field, i have not only the date but also the time, like that: '2018-07-06 15:30:40+00' but i think that is not a problem, according to what i read, date_part function on postgree should works even with time. As related on postgree site, the function date_part should be used like this model: date_part('day', timestamp '2001-02-16 20:38:40'). So i tried this and worked:



If you pain attention on my example above, i put the date on quotes because if i don't do it, i will get an error. The transformation toChar() that i made on my code is because of this rule on date_part function
 
Tim Holloway
Bartender
Posts: 19972
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
thomas topázio
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.



I tried how you wrote:



but i'm still getting the error:

ERROR:  column "final_date" does not exist
LINE 21:    DATE_PART('day', final_date::timestamp -  initial_date...
HINT:  There is a column named " final_date" in table " final_date", but it cannot be referenced from this part of the query.


How i said to Dave, i guess this error is because i'm putting the alias on the diff and is not accepted
 
Tim Holloway
Bartender
Posts: 19972
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
thomas topázio
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.



I'm sorry, my doubt is making my expression difficult. Let me try again and thanks for you patience:

My book table has 3 fields: id, time, status

For example, let's suppose that i have the following data: 1234 (id), 2018-07-27 19:20:42+00 (time), planning (status)

And in some moment, after few days, my task identified by id 1234 changed the status to closed, so now i have: 1234 (id), 2018-07-30 10:00:00+00 (time), closed (status)

If i do that (i  improved my code compared to what I posted in the first topic):




Is returned for me: 1234 (id), 2018-07-27 19:20:42+00 (time when task was with status planning), 1234 (id), 2018-07-30 10:00:00+00 (time when task was with status closed)


Now i just need to calculate the diff between these two dates but i can't figure it how
 
Tim Holloway
Bartender
Posts: 19972
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was afraid you were going to say that.

SQL is not well-designed for this kind of thing. It can work on horizontal slices and on vertical slices, but it has no simple syntax for inter-row operations such as this.

Your simplest approach would either be a stored procedure or an independent application (for example, in Java code).
 
thomas topázio
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay, guys! After all this discussion i solved my problem. I will post the code bellow. Thanks for helping me, everyone's opinion was important!  

 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!