Why does days number count not work in Postgresql?

Question

I tried and it is OK

postgres=# SELECT CURRENT_TIMESTAMP;
       current_timestamp       
-------------------------------
 2020-05-22 09:31:19.492038+02
(1 row)

But

postgres=# SELECT EXTRACT(DAY FROM DATE('2020-01-01'));
 date_part 
-----------
         1
(1 row)

I want to count days in this year.

PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu

select extract(doy from current_date);
 date_part 
-----------
       143
(1 row)

score 1 · Accepted Answer · 2020-05-22 08:22:11Z

1

Extracting a day part from an interval does not do what you think it does, e.g. the resulting interval could be 5 months 6 days and then the extract would return 6.

If you just want the number of days for the current date, use doy

select extract(doy from current_date);

which is a shortcut for

select current_date - date_trunc('year', current_date)::date

Online example

Edit

Your timestamp calculation doesn't include the full day of the last day, and - as explained - extract(day from ..) extract the day part of the interval not the length of the interval in days.

Additionally, to_timestamp() returns a timestamp WITH time zone which also takes into account that one of the days in your interval is shorter than 24 hours:

You can see this when looking at the result without using extract:

select to_timestamp('2020-05-22', 'yyyy-mm-dd') - to_timestamp('2020-01-01', 'yyyy-mm-dd') as c1,
       to_timestamp('2020-05-22 23:59:59', 'yyyy-mm-dd hh24:mi:ss') - to_timestamp('2020-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') as c2

returns

c1                                               | c2                                                
-------------------------------------------------+---------------------------------------------------
0 years 0 mons 141 days 23 hours 0 mins 0.0 secs | 0 years 0 mons 142 days 22 hours 59 mins 59.0 secs

edited May 22, 2020 at 8:22

answered May 22, 2020 at 8:01

user330315

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

4 Comments

MikiBelavista Over a year ago

Thanks for helping out, why does my answer give -141?

pifor Over a year ago

Because the 22-MAY is the day number 141 of the current year and you subtracted days from the 01-JAN to 22-MAY instead of 22-MAY from 01-JAN.

MikiBelavista Over a year ago

But take a look at my edit, accepted solution returns 143!

pifor Over a year ago

OK. Day of the year is 143 and not 141. They are likely rounding issues because you cast to TIMESTAMP which adds hour, days, minutes, seconds, etc which is here not needed if you need only the number of days.

MikiBelavista · Accepted Answer · 2020-05-22 07:53:21Z

0

This works for me

postgres=# SELECT EXTRACT(DAY FROM TO_TIMESTAMP('2020-01-01', 'YYYY-MM-DD')-TO_TIMESTAMP('2020-05-22', 'YYYY-MM-DD'));
 date_part 
-----------
      -141
(1 row)

answered May 22, 2020 at 7:53

MikiBelavista

2,88615 gold badges57 silver badges83 bronze badges

Collectives™ on Stack Overflow

Why does days number count not work in Postgresql?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related