Difference between 2 dates in PostgreSQL

Question

I have 2 "timestamp" columns in a table, in a Postgres database. I need to extract the difference in days between these 2 columns. Let's consider the columns are "column1" and "column2". And the difference between these 2 timestamps is 10 hours. So, for the query:

select column2 - column1 from table

The result is of "interval" type, and it is "10:00:00". I need to convert this in a real number, and, for this example, to obtain the result as something like "0.41666...7"

I need to convert this in a real number I am not sure what you mean? For example what would be the value for 10:32:41? — Rahul Tripathi
– Rahul Tripathi, Commented Oct 15, 2015 at 10:55

user330315 · Accepted Answer · 2015-10-15 11:26:50Z

3

You can get the number of seconds from the resulting interval using extract()

select extract(epoch from column2 - column1)
from the_table;

If you divide that by 86400 to get it as a fraction of a day

answered Oct 15, 2015 at 11:26

user330315

Sign up to request clarification or add additional context in comments.

Comments

Sammy · Accepted Answer · 2015-10-15 10:56:53Z

2

DATE_PART('day', column2 - column1)

http://www.sqlines.com/postgresql/how-to/datediff

answered Oct 15, 2015 at 10:56

Sammy

4773 silver badges13 bronze badges

3 Comments

Catalin Vladu Over a year ago

This returns an integer value. And I need a value with decimals. With date_part, the result is "0".

Sammy Over a year ago

See the reference I linked. DATE_PART('hour', end - start )/24 should give you the extra fractional part of a day. So whole answer would be DATE_PART('day', column2 - column1) + DATE_PART('hour', end - start )/24

wasabigeek Over a year ago

This seems like it might look purely at the minute, without consideration of the other denominators e.g. for DATE_PART('minute', time_1 - time_2) < 1, I'm getting time_1 = 2019-06-21 09:32:45.073002 and time_2 = 2019-06-24 04:32:47.779387 returned

Collectives™ on Stack Overflow

Difference between 2 dates in PostgreSQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related