Getting day difference in 2 dates in postgres

Question

I have a table which looks like this:

datestamp
2020-04-01
2020-04-02

I am trying to do two things:

Convert datestamp to date format, because right now it's ABC
Get difference between datestamp and the end of this year ( 2020-12-31 )

So that I would get a result like this:

datestamp      diff
2020-04-01     275
2020-04-02     274

What I've tried:

DATEDIFF(day, datestamp, '2020-12-31 00:00:00.0000000')

I get:

DATEDIFF(day, datestamp, '2020-12-31 00:00:00.0000000')

Where is my mistake?

Where in the manual did you find datediff()

user330315
– user330315

2020-04-02 12:15:01 +00:00
Commented Apr 2, 2020 at 12:15 — user330315
– user330315, Commented Apr 2, 2020 at 12:15

user330315 · Accepted Answer · 2020-04-02 12:15:38Z

2

Simply subtract the values:

select datestamp, date '2020-12-31' - datestamp as diff
from the_table;

This assumes that datestamp is a date column.

answered Apr 2, 2020 at 12:15

user330315

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

2 Comments

Jonas Palačionis Over a year ago

At the moment datestamp is a text column, what would be the easiest way to convert it to date?

user330315 Over a year ago

@JonasPalačionis: as documented in the manual cast(datestamp as date) or datestamp::date - but that only works if all values can be cast without an error

Collectives™ on Stack Overflow

Getting day difference in 2 dates in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related