How Can i cast the Date datatype into integer?(postgresql)

Question

I want the no of days into a column which is defined as integer in a table i.e.,

select age('2013-04-06','2013-04-04') --> gives me 2days as output

i want that 2 days to be stored into a column with datatype integer.. i tried this but i am getting the no of hours from this query..

SELECT (EXTRACT(epoch FROM (select age('2013-07-06','2013-07-04')))/3600); --> 48 i.e., 48 hours as output

i need the integer value(2) from the 2 days or from the 48 hours

How to get this to be done?

Craig Ringer · Accepted Answer · 2013-07-09 08:43:39Z

4

age(...) produces an interval. You can then use extract on the interval to get the days:

select extract(day from age('2013-04-06','2013-04-04'));

With some inputs you'd want to justify_interval the interval before calling extract on it - but age produces a pre-justified interval, so that shouldn't be needed in this case.

answered Jul 9, 2013 at 8:43

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

3 Comments

Richard Huxton Over a year ago

Or for dates (not timestamps), just subtract the one from the other. That gives you an integer number of days.

Craig Ringer Over a year ago

@RichardHuxton Good point; I tend to forget that, mostly because I rarely use raw date.

09Q71AO534 Over a year ago

select extract(day from age('2013-07-02','2013-06-01')); will not work in getting 32 days!! What is the other way to get the days..@craig ringer

09Q71AO534 · Accepted Answer · 2013-07-31 13:14:02Z

2

select '2014-05-02'::date - '2013-04-01'::date;

gives the No of days even its difference between day, month or year

answered Jul 31, 2013 at 13:14

09Q71AO534

4,47014 gold badges47 silver badges69 bronze badges

2 Comments

09Q71AO534 Over a year ago

@Craig Ringer Will this solve the purpose or using this thing ,or it will have any performance changes !

Craig Ringer Over a year ago

Simple date subtraction is fine. There is no reason not to do it. You can subtract from current_date if you want a distance from now.

Collectives™ on Stack Overflow

How Can i cast the Date datatype into integer?(postgresql)

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related