CODEWITHSUNDEEP
sqlpostgresqldateselect
java
java

Reputation: 1214

How to get how many days passed since start of this year?

I have a query which uses needs to know how many days passed since 1st of January in the current year.

Which means that if the query runs for example in:

2nd Jan 2017 than it should return 2 (as 2 days passed since 1st Jan 2017).

10th Feb 2016 than it should return 41 (as 41 days passed since 1st Jan 2016).

basically it needs to take Current Year from Curent Date and count the days since 1/1/(Year).

i have the current year with: SELECT EXTRACT(year FROM CURRENT_DATE);

I created the 1st of Jan with:

select (SELECT EXTRACT(year FROM CURRENT_DATE)::text || '-01-01')::date

How do I get the difference from this date to Current_Date?

Basically this question can be Given two dates, how many days between them?

Something like age(timestamp '2016-01-01', timestamp '2016-06-15') isn't good because I need the result only in days. while age gives in years,months and days.

Upvotes: 1

Views: 2141

Answers (3)

snso
snso

Reputation: 329

Another solution is to use DATEDIFF 

 SELECT DATE_PART('day', now()::timestamp - '2016-01-01 00:00:00'::timestamp);

Upvotes: 1

Mureinik
Mureinik

Reputation: 311228

An easier approach may be to extract the day of year ("doy") field from the date:

db=> SELECT EXTRACT(DOY FROM CURRENT_DATE);
 date_part 
-----------
        41

And if you need it as a number, you could just cast it:

db=> SELECT EXTRACT(DOY FROM CURRENT_DATE)::int;
 date_part 
-----------
        41

Note: The result 41 was produced by running the query today, February 9th.

Upvotes: 4

user330315
user330315

Reputation:

Given two dates, how many days between them

Just subtract one from the other.

In your case you could just round the current_date to the start of the year and subtract that from the current date:

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

The ::date cast is necessary to get the result as an integer, otherwise the result will be an interval.

Upvotes: 1

Related Questions