CODEWITHSUNDEEP
sqlpostgresqldate
zermy
zermy

Reputation: 621

How can I compute a duration from two dates?

I have this query, that's been giving me some issues, it looks like this:

UPDATE servicecontracts 
   SET planned_duration = (to_char(due_date) - to_char(start_date) + 1)
      ,actual_duration =''
      ,progress = NULL 
 WHERE servicecontractsid = '263'

After some research, I managed to figure out what this query is trying to do, it' s just trying to find the planned duration, by subtracting the due date and the start date. Why, this is trying to do that by subtracting strings, I do not know. Also, the to_char function requires a second parameter.

So, anyway, now I need to find the planned_duration, but how do I do that. According to the Postgresql docs, the to_char function doesn't have an option to return an integer, if you set it to return text and then if you try to convert the string into an integer using explicit casts, like ::integer, you get an error because an integer can't have colons in there.
So, is there a way for to_char to return an integer that somehow represents the date, and then subtract the two? If not, what should I do to carry this out?

Upvotes: 0

Views: 594

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

I quote from the fine manual here

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');

Result: 982384720.12

But for computing intervals, there is simpler way:

SELECT (due_date - start_date)

Upvotes: 2

Paul Bellora
Paul Bellora

Reputation: 55213

Just subtracting two date types from each other should return their difference in days.

SET planned_duration = (due_date - start_date)

Not sure why to_char is being used, unless I'm missing something here.

Upvotes: 1

Related Questions