Adelson Dias Adelson Dias - 25 days ago 8
SQL Question

Converting date into integer (1 to 365)

I have no idea if there is a function in postgres to do that, but how can I convert a date (yyyy-mm-dd) into the numeric correspondent in SQL?

E.g. table input

id | date
------+-------------
1 | 2013-01-01
2 | 2013-01-02
3 | 2013-02-01


Output

id | date
------+-------------
1 | 1
2 | 2
3 | 32

Answer

You are looking for the extract() function with the doy("Day Of Year") argument, not day ("day of the week"):

select id, extract(doy from "date") 
from the_table;
Comments