Re: [HACKERS] RE: [SQL] datediff function - Mailing list pgsql-hackers

Here the SQL/92 expression supported also by PostgreSQL:
select extract(day from date '1999-01-02') - extract(day from date
'1999-01-01');

José

Datediff in MS-SQL gives you the number of boundaries between two dates.
DATEDIFF(day, '1/1/99 23:59:00', '1/2/99 00:01:00') gives 1
DATEDIFF(day, '1/2/99 00:01:00', '1/2/99 00:03:00') gives 0
The {PostgreSQL|postgres|pgsql|whatever} way of doing it is much nicer.

> > select datediff(minute, timein, timeout) as totaltime from schedule
> >
> > It would give me the number 60, that's it. I don't want any
> qualifier behind
> > the number since it blew up the stupid microsoft ADO driver like you
> > wouldn't believe.
>
> If you don't want to write 'now'::datetime you can always write
> datetime('now'). Same goes for '1 week'::timespan and
espan( extract(day from date '1999-01-02') - extract(day from date '1999
-01-01');


John Ridout ha scritto:

> I unfortunately do MS-SQL.
> > '1 week' ).
> > I don't think this will blow up your Microsoft product, but then again,
> > anything can blow up a Microsoft product, being a Microsoft Product
> > included...
> >
> > To make things clear, here is what Postgres can and cannot do:
> >
> > It can give you the interval between two dates. The returned value is an
> > integer representing the number of days between them.
> >
> > It can give you the interval between two datetimes. The returned
> > value is a
> > timespan, expressing days, hours, minutes, etc. as needed.
> >
> > Another method to get the same thing is using age( datetime1, datetime2 ).
> > This returns a timespan, but expressed in years, months, days, hours and
> > minutes. There is a subtle difference here, because a year is not always
> > 365 days, and a month is 28-31 days, depending...
> >
> > You can also truncate datetimes, dates, and other date related types, to
> > the part of your choice. Truncate it to the minute, and it drops the
> > seconds, and gives it back to you with 00 in the seconds. Truncate it to
> > days and it gives it back to you at 00:00:00. This is done with
> > date_trunc().
> >
> > Another useful operation which can be done is taking one part of the
> > datetime (or related type). For example, the minutes, the
> > seconds, the day,
> > the day of week, or the seconds since the epoch.
> >
> > Now, I'm not sure these functions do exactly what you wanted. It
> > depends on
> > what you expect from datediff(minute, timein, itmeout) when they
> > are not on
> > the same day. For 13-oct-1999 14:00:00 and 14-oct-1999 14:00:05, do you
> > expect 5 or 24*60 + 5?
> >
> > If only 5, then you can do it with
> >
> > SELECT date_part( 'minute', datetime1 - datetime2 )
> >
> > If not, you will have to do the 24*60 calculation in full.
> >
> > Herouth
> >
> > --
> > Herouth Maoz, Internet developer.
> > Open University of Israel - Telem project
> > http://telem.openu.ac.il/~herutma
> >
> >
> >
> >