pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddatediff function
12+ messages / 8 participants
[nested] [flat]
* datediff function
@ 1999-08-16 16:02 Pham, Thinh <tpham@mail.priority.net>
0 siblings, 2 replies; 12+ messages in thread
From: Pham, Thinh @ 1999-08-16 16:02 UTC (permalink / raw)
To: pgsql-sql
Hi everyone,
Does anyone know if postgres has a function similar to what datediff does in
mssql server? I need it to do an update similar to the one below:
"update schedule set purged = 0 where datediff(day, timein, getdate()) > 30"
I know i could pull the whole table down to my machine, modify the data and
then upload it back, but that's really stupid not to mention what it'll do
to network trafic.
Thank you very much for any answer,
Thinh
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [SQL] datediff function
@ 1999-08-16 23:26 tjk@tksoft.com <tjk@tksoft.com>
parent: Pham, Thinh <tpham@mail.priority.net>
1 sibling, 0 replies; 12+ messages in thread
From: tjk@tksoft.com @ 1999-08-16 23:26 UTC (permalink / raw)
To: tpham@mail.priority.net; +Cc: pgsql-sql
I think what you are looking for is age()
E.g.
"update schedule set purged = 0 where age('now',dayin) > timespan('30 days'::reltime)"
Presuming a table such as this:
create table schedule (purged int, dayin datetime);
This replaces "day" and "timein" with "dayin."
Troy
>
> Hi everyone,
>
> Does anyone know if postgres has a function similar to what datediff does in
> mssql server? I need it to do an update similar to the one below:
>
> "update schedule set purged = 0 where datediff(day, timein, getdate()) > 30"
>
> I know i could pull the whole table down to my machine, modify the data and
> then upload it back, but that's really stupid not to mention what it'll do
> to network trafic.
>
> Thank you very much for any answer,
> Thinh
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [SQL] datediff function
@ 1999-08-17 11:05 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Pham, Thinh <tpham@mail.priority.net>
1 sibling, 0 replies; 12+ messages in thread
From: Herouth Maoz @ 1999-08-17 11:05 UTC (permalink / raw)
To: tjk@tksoft.com <tjk@tksoft.com>; tpham@mail.priority.net; +Cc: pgsql-sql
At 02:26 +0300 on 17/08/1999, tjk@tksoft.com wrote:
>
> I think what you are looking for is age()
> E.g.
>
> "update schedule set purged = 0 where age('now',dayin) > timespan('30
>days'::reltime)"
>
> Presuming a table such as this:
>
> create table schedule (purged int, dayin datetime);
>
> This replaces "day" and "timein" with "dayin."
Basically correct, but if there is an index on dayin, it won't be used. The
best query to do would be
WHERE dayin > 'now'::datetime - '30 days'::timespan;
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: [SQL] datediff function
@ 1999-08-17 13:18 Pham, Thinh <tpham@mail.priority.net>
0 siblings, 2 replies; 12+ messages in thread
From: Pham, Thinh @ 1999-08-17 13:18 UTC (permalink / raw)
To: pgsql-sql
What happen if i just want to compare using minute only or hour only instead
of day? Is there a function to do that or is postgres only work in day?
T.
> -----Original Message-----
> From: Herouth Maoz [mailto:herouth@oumail.openu.ac.il]
> Sent: Tuesday, August 17, 1999 6:05 AM
> To: tjk@tksoft.com; tpham@mail.priority.net
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] datediff function
>
>
> At 02:26 +0300 on 17/08/1999, tjk@tksoft.com wrote:
>
>
> >
> > I think what you are looking for is age()
> > E.g.
> >
> > "update schedule set purged = 0 where age('now',dayin) >
> timespan('30
> >days'::reltime)"
> >
> > Presuming a table such as this:
> >
> > create table schedule (purged int, dayin datetime);
> >
> > This replaces "day" and "timein" with "dayin."
>
> Basically correct, but if there is an index on dayin, it
> won't be used. The
> best query to do would be
>
> WHERE dayin > 'now'::datetime - '30 days'::timespan;
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: [SQL] datediff function
@ 1999-08-17 13:28 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Pham, Thinh <tpham@mail.priority.net>
1 sibling, 0 replies; 12+ messages in thread
From: Herouth Maoz @ 1999-08-17 13:28 UTC (permalink / raw)
To: Pham, Thinh <tpham@mail.priority.net>; pgsql-sql
At 16:18 +0300 on 17/08/1999, Pham, Thinh wrote:
> What happen if i just want to compare using minute only or hour only instead
> of day? Is there a function to do that or is postgres only work in day?
No problem, just write 'now'::datetime - '6 hours'::timespan. Or some such.
Please read about the datetime and timespan types in the user guide.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [SQL] datediff function
@ 1999-08-17 13:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pham, Thinh <tpham@mail.priority.net>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 1999-08-17 13:34 UTC (permalink / raw)
To: Pham, Thinh <tpham@mail.priority.net>; +Cc: pgsql-sql
"Pham, Thinh" <tpham@mail.priority.net> writes:
> What happen if i just want to compare using minute only or hour only instead
> of day? Is there a function to do that or is postgres only work in day?
See date_part().
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: [SQL] datediff function
@ 1999-08-17 14:37 Pham, Thinh <tpham@mail.priority.net>
0 siblings, 1 reply; 12+ messages in thread
From: Pham, Thinh @ 1999-08-17 14:37 UTC (permalink / raw)
To: pgsql-sql
Thank you for answering all my stupid questions. I did read the User manual
as well as Programmer and Admin, and what i was able to get out was not
much. I guess i wasn't used to the way that postgres work coming from a
microsoft background. But wouldn't you agree that the manuals was a little
brief and criptic at times? My question is how do i go about writing some
more documentations and examples and submit it for inclusion into the
manual. It just so that the next person w/ the same background as i would be
able to understand those functions quickly and be able to use the example
and apply it to his program w/o asking all those same basic questions which
i'm sure you guys are tired of answering.
Also, is it the same thing for submitting documentation as well as function
into postgres? What i really need was the total amount of time between 2
separate point of times. If i ask for 'day' then it return day, 'minute'
then it return minutes. For example:
Timein Timeout
Tue Aug 17 15:00:00 1999 CDT Tue Aug 17 16:00:00 1999 CDT
select datediff(day, timein, timeout) as totaltime from schedule
Would give me a _number_ 0 since it's the same day, and if i used minute as
below:
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.
Thank you,
Thinh
> -----Original Message-----
> From: Herouth Maoz [mailto:herouth@oumail.openu.ac.il]
> Sent: Tuesday, August 17, 1999 8:29 AM
> To: Pham, Thinh; 'pgsql-sql@postgreSQL.org'
> Subject: RE: [SQL] datediff function
>
>
> At 16:18 +0300 on 17/08/1999, Pham, Thinh wrote:
>
>
> > What happen if i just want to compare using minute only or
> hour only instead
> > of day? Is there a function to do that or is postgres only
> work in day?
>
> No problem, just write 'now'::datetime - '6 hours'::timespan.
> Or some such.
> Please read about the datetime and timespan types in the user guide.
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: [SQL] datediff function
@ 1999-08-17 15:24 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Pham, Thinh <tpham@mail.priority.net>
0 siblings, 1 reply; 12+ messages in thread
From: Herouth Maoz @ 1999-08-17 15:24 UTC (permalink / raw)
To: Pham, Thinh <tpham@mail.priority.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: [SQL] datediff function
@ 1999-08-17 15:45 John Ridout <johnridout@ctasystems.co.uk>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 12+ messages in thread
From: John Ridout @ 1999-08-17 15:45 UTC (permalink / raw)
To: pgsql-hackers <pgsql-hackers@postgresql.org>
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
>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [HACKERS] RE: [SQL] datediff function
@ 1999-08-23 14:02 José Soares <jose@sferacarta.com>
parent: John Ridout <johnridout@ctasystems.co.uk>
0 siblings, 0 replies; 12+ messages in thread
From: José Soares @ 1999-08-23 14:02 UTC (permalink / raw)
To: John Ridout <johnridout@ctasystems.co.uk>; +Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
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
> >
> >
> >
> >
^ permalink raw reply [nested|flat] 12+ messages in thread
* DateDiff() function
@ 2013-07-11 05:17 Huan Ruan <huan.ruan.it@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Huan Ruan @ 2013-07-11 05:17 UTC (permalink / raw)
To: pgsql-sql
Hi Guys
We are migrating to Postgres. In the current system, we use datediff()
function to get the difference between two dates, e.g. datediff (month,
cast('2013-01-01' as timestamp), cast('2013-02-02' as timestamp) returns 1.
I understand that Postgres has Interval data type so I can achieve the same
with Extract(month from Age(date1, date2)). However, I try to make it so
that the existing SQL can run on both databases without changes. One
possible way is to add a datediff function to Postgres, but the problem is
that month/day/year etc is a keyword not a string like 'month'. I noticed
that Postgres seems to convert Extract(month from current_timestamp) to
date_part('month', current_timestamp), you can also do Extract('month' from
current_timestamp). So it seems internally, Postgres can do the mapping
from month to 'month'. I was wondering if there is a way for me to do the
same for the datediff() function? Any other ideas?
Thanks
Huan
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: DateDiff() function
@ 2013-07-11 06:18 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: Huan Ruan <huan.ruan.it@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Gavin Flower @ 2013-07-11 06:18 UTC (permalink / raw)
To: Huan Ruan <huan.ruan.it@gmail.com>; +Cc: pgsql-sql
On 11/07/13 17:17, Huan Ruan wrote:
> Hi Guys
>
> We are migrating to Postgres. In the current system, we use datediff()
> function to get the difference between two dates, e.g. datediff
> (month, cast('2013-01-01' as timestamp), cast('2013-02-02'
> as timestamp) returns 1.
>
> I understand that Postgres has Interval data type so I can achieve the
> same with Extract(month from Age(date1, date2)). However, I try to
> make it so that the existing SQL can run on both databases without
> changes. One possible way is to add a datediff function to Postgres,
> but the problem is that month/day/year etc is a keyword not a string
> like 'month'. I noticed that Postgres seems to convert Extract(month
> from current_timestamp) to date_part('month', current_timestamp), you
> can also do Extract('month' from current_timestamp). So it seems
> internally, Postgres can do the mapping from month to 'month'. I was
> wondering if there is a way for me to do the same for the datediff()
> function? Any other ideas?
>
> Thanks
> Huan
Purely out of curiosity, could you tell us what database software you
are moving from, as well as a rough idea of the size of database, type
and volume of database queries?
It would also be of interest to know what postgres features in
particular were the biggest motivations for change, and any aspects that
gave you cause for concern - obviously overall, it must have come across
as being better .
I strongly suspect that answering these questions will have no direct
bearing on how people will answer your query! :-)
Cheers,
Gavin
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2013-07-11 06:18 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-16 16:02 datediff function Pham, Thinh <tpham@mail.priority.net>
1999-08-16 23:26 ` tjk@tksoft.com <tjk@tksoft.com>
1999-08-17 11:05 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-17 13:18 RE: [SQL] datediff function Pham, Thinh <tpham@mail.priority.net>
1999-08-17 13:28 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-17 13:34 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-17 14:37 RE: [SQL] datediff function Pham, Thinh <tpham@mail.priority.net>
1999-08-17 15:24 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-17 15:45 ` John Ridout <johnridout@ctasystems.co.uk>
1999-08-23 14:02 ` José Soares <jose@sferacarta.com>
2013-07-11 05:17 DateDiff() function Huan Ruan <huan.ruan.it@gmail.com>
2013-07-11 06:18 ` Re: DateDiff() function Gavin Flower <GavinFlower@archidevsys.co.nz>
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