5

I'm trying to convert a date (date type) into int. This int should be something like the number of days since the 1 January 1900. How to get this in postgresql? In excel I'm getting this automatically when i concatenate a date with a string. Example : 2011/11/01 convert into int as 36831

Improve this question

1 Answer 1

Reset to default
4

Simply subtract the two dates:

select date '2011-11-01' - date '1900-01-01'

the result will be the number of days.

More details in the manual:
http://www.postgresql.org/docs/current/static/functions-datetime.html

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

8 Comments

One hundred years is roughly 36,500 days, right? So the OP's arithmetic works out to about 101 years between 1900 and 2011. I don't think I trust that answer. I'd much rather trust PostgreSQL's subtraction operator.
@MikeSherrill'CatRecall': not sure which answer you mean with "not trust". But you are right: Postgres returns 40846 for my example
From OP: "Example : 2011/11/01 convert into int as 36831"
Thank you for the answer... But it's so confuse, in the excel, i can use this function: =datevalue("2011/11/01") for getting the serial number as 36831. But, in postgre... I tried the subtract as your answer, the result was not 36831 When i try this : (select date '2011-11-01' - date '1900-01-01') +2 in postgre, the result was same as 36831. Why it must add 2...i' m not sure with my query :-(
@nidazakiy Apparently Excel does not use 1900-01-01 as the base then. If you run select date '2011-11-01' - 36831 you will see that this evaluates to 1910-12-30 so I guess that's what Excel is using . Btw: the name is either Postgres or PostgreSQL but not Postgre
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.