From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Pham, Thinh <tpham@mail.priority.net>
To: 'pgsql-sql@postgreSQL.org' <pgsql-sql@postgreSQL.org>
Subject: RE: [SQL] datediff function
Date: Tue, 17 Aug 1999 18:24:26 +0300
Message-ID: <l03130304b3df2a36b34d@[147.233.159.109]> (raw)
In-Reply-To: <551413BBBB03D111BF880060B01AACCE139F35@home.priority.net>
At 17:37 +0300 on 17/08/1999, Pham, Thinh wrote:
> 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 timespan( '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: herouth@oumail.openu.ac.il, tpham@mail.priority.net
Subject: RE: [SQL] datediff function
In-Reply-To: <l03130304b3df2a36b34d@[147.233.159.109]>
* 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