RE: [SQL] datediff function

I unfortunately do MS-SQL.
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( 
> '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
> 
> 
> 
>