From: John Ridout <johnridout@ctasystems.co.uk>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: RE: [SQL] datediff function
Date: Tue, 17 Aug 1999 16:45:52 +0100
Message-ID: <000301bee8c7$95ac8540$6701010a@f6s5f3> (raw)
In-Reply-To: <l03130304b3df2a36b34d@[147.233.159.109]>
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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: johnridout@ctasystems.co.uk, pgsql-hackers@postgresql.org
Subject: RE: [SQL] datediff function
In-Reply-To: <000301bee8c7$95ac8540$6701010a@f6s5f3>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox