pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubtract a day from the NOW function
19+ messages / 9 participants
[nested] [flat]
* subtract a day from the NOW function
@ 2007-06-07 16:08 Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 21:41 ` Re: subtract a day from the NOW function Osvaldo Kussama <osvaldo_kussama@yahoo.com.br>
0 siblings, 2 replies; 19+ messages in thread
From: Campbell, Lance @ 2007-06-07 16:08 UTC (permalink / raw)
To: pgsql-sql
Table
Field "some_timestamp" is a timestamp.
In a "WHERE" statement I need to compare a timestamp field in a table
"some_timestamp" to now() - one day.
Example:
SELECT some_timestamp WHERE to_char(some_timestamp, 'YYYYMMDD') >
(to_char(now(), 'YYYYMMDD') - 1 day);
The statement "to_char(now(), 'YYYYMMDD') - 1 day)" is obviously
incorrect. I just need to know how to form this in a way that will
work.
If there is an entirely different solution I am all for it. Do note
that I started down this path because I want to exclude the hour,
minutes and seconds found in the field "some_timestamp" and in the
function now().
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
@ 2007-06-07 16:36 ` Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:39 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 2 replies; 19+ messages in thread
From: Campbell, Lance @ 2007-06-07 16:36 UTC (permalink / raw)
To: pgsql-sql
I just figured it out. The solution is:
select to_char((now() - interval '1 day'), 'YYYYMMDD');
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
________________________________
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Campbell, Lance
Sent: Thursday, June 07, 2007 11:09 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] subtract a day from the NOW function
Table
Field "some_timestamp" is a timestamp.
In a "WHERE" statement I need to compare a timestamp field in a table
"some_timestamp" to now() - one day.
Example:
SELECT some_timestamp WHERE to_char(some_timestamp, 'YYYYMMDD') >
(to_char(now(), 'YYYYMMDD') - 1 day);
The statement "to_char(now(), 'YYYYMMDD') - 1 day)" is obviously
incorrect. I just need to know how to form this in a way that will
work.
If there is an entirely different solution I am all for it. Do note
that I started down this path because I want to exclude the hour,
minutes and seconds found in the field "some_timestamp" and in the
function now().
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
@ 2007-06-07 16:39 ` Campbell, Lance <lance@uiuc.edu>
1 sibling, 0 replies; 19+ messages in thread
From: Campbell, Lance @ 2007-06-07 16:39 UTC (permalink / raw)
To: pgsql-sql
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
________________________________
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Campbell, Lance
Sent: Thursday, June 07, 2007 11:37 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] subtract a day from the NOW function
I just figured it out. The solution is:
select to_char((now() - interval '1 day'), 'YYYYMMDD');
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
________________________________
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Campbell, Lance
Sent: Thursday, June 07, 2007 11:09 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] subtract a day from the NOW function
Table
Field "some_timestamp" is a timestamp.
In a "WHERE" statement I need to compare a timestamp field in a table
"some_timestamp" to now() - one day.
Example:
SELECT some_timestamp WHERE to_char(some_timestamp, 'YYYYMMDD') >
(to_char(now(), 'YYYYMMDD') - 1 day);
The statement "to_char(now(), 'YYYYMMDD') - 1 day)" is obviously
incorrect. I just need to know how to form this in a way that will
work.
If there is an entirely different solution I am all for it. Do note
that I started down this path because I want to exclude the hour,
minutes and seconds found in the field "some_timestamp" and in the
function now().
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
@ 2007-06-07 17:27 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
1 sibling, 1 reply; 19+ messages in thread
From: Michael Glaesemann @ 2007-06-07 17:27 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-sql
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Campbell, Lance
> Sent: Thursday, June 07, 2007 11:09 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] subtract a day from the NOW function
> SELECT some_timestamp WHERE to_char(some_timestamp, ‘YYYYMMDD’) >
> (to_char(now(), ‘YYYYMMDD’) – 1 day);
On Jun 7, 2007, at 11:36 , Campbell, Lance wrote:
> select to_char((now() - interval '1 day'), 'YYYYMMDD');
Why are you using to_char? Timestamps and dates support comparisons
just fine.
SELECT CURRENT_TIMESTAMP > (CURRENT_TIMESTAMP - INTERVAL '1 day');
?column?
----------
t
(1 row)
CURRENT_TIMESTAMP is SQL-spec for now().
If you're specifically looking to compare dates rather than
timestamps, you can cast timestamp to date:
SELECT CURRENT_DATE > (CURRENT_DATE - INTERVAL '1 day')::date;
?column?
----------
t
(1 row)
You could also use the age function:
SELECT age(CURRENT_TIMESTAMP) < INTERVAL '1 day';
SELECT age(CURRENT_TIMESTAMP) < INTERVAL '1 day';
?column?
----------
t
(1 row)
Hope that helps.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
@ 2007-06-07 17:49 ` Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Re: subtract a day from the NOW function Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 2 replies; 19+ messages in thread
From: Campbell, Lance @ 2007-06-07 17:49 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-sql
Michael,
So based on your feedback would it be better to do option A or B below?
1) I have a timestamp field, "some_timestamp", in table "some_table".
2) I want to compare field "some_timestamp" to the current date - 1 day.
I need to ignore hours, minutes and seconds.
Possible options:
A) SELECT * FROM some_table WHERE some_timestamp::date > (CURRENT_DATE -
INTERVAL '1 day')::date
Or
B) SELECT * FROM some_table WHERE to_char(some_timestamp, 'YYYYMMDD') >
to_char((now() - interval '1 day'), 'YYYYMMDD');
I am just guessing but A does seem like it would be a better option.
Option A is at least cleaner to read.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
-----Original Message-----
From: Michael Glaesemann [mailto:grzm@seespotcode.net]
Sent: Thursday, June 07, 2007 12:27 PM
To: Campbell, Lance
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] subtract a day from the NOW function
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Campbell, Lance
> Sent: Thursday, June 07, 2007 11:09 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] subtract a day from the NOW function
> SELECT some_timestamp WHERE to_char(some_timestamp, 'YYYYMMDD') >
> (to_char(now(), 'YYYYMMDD') - 1 day);
On Jun 7, 2007, at 11:36 , Campbell, Lance wrote:
> select to_char((now() - interval '1 day'), 'YYYYMMDD');
Why are you using to_char? Timestamps and dates support comparisons
just fine.
SELECT CURRENT_TIMESTAMP > (CURRENT_TIMESTAMP - INTERVAL '1 day');
?column?
----------
t
(1 row)
CURRENT_TIMESTAMP is SQL-spec for now().
If you're specifically looking to compare dates rather than
timestamps, you can cast timestamp to date:
SELECT CURRENT_DATE > (CURRENT_DATE - INTERVAL '1 day')::date;
?column?
----------
t
(1 row)
You could also use the age function:
SELECT age(CURRENT_TIMESTAMP) < INTERVAL '1 day';
SELECT age(CURRENT_TIMESTAMP) < INTERVAL '1 day';
?column?
----------
t
(1 row)
Hope that helps.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
@ 2007-06-07 18:12 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:58 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 1 reply; 19+ messages in thread
From: Scott Marlowe @ 2007-06-07 18:12 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
Campbell, Lance wrote:
> Michael,
> So based on your feedback would it be better to do option A or B below?
>
> 1) I have a timestamp field, "some_timestamp", in table "some_table".
> 2) I want to compare field "some_timestamp" to the current date - 1 day.
> I need to ignore hours, minutes and seconds.
>
You might want to use date_trunc then:
select * from sometable where date_trunc('day',tiemstampfield) >
date_trunc('day',now() - interval '1 day');
or something like that.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Re: subtract a day from the NOW function Scott Marlowe <smarlowe@g2switchworks.com>
@ 2007-06-07 18:58 ` Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 19:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 19+ messages in thread
From: Steve Crawford @ 2007-06-07 18:58 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Campbell, Lance <lance@uiuc.edu>; Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
Scott Marlowe wrote:
> Campbell, Lance wrote:
>> Michael,
>> So based on your feedback would it be better to do option A or B below?
>>
>> 1) I have a timestamp field, "some_timestamp", in table "some_table".
>> 2) I want to compare field "some_timestamp" to the current date - 1 day.
>> I need to ignore hours, minutes and seconds.
>>
> You might want to use date_trunc then:
>
> select * from sometable where date_trunc('day',tiemstampfield) >
> date_trunc('day',now() - interval '1 day');
>
> or something like that.
Beware in the "or something like that category" that PostgreSQL
considers "1 day" to be "24 hours" thus depending on whether the
timestampfield is with or without TZ and where you do your truncation
(before or after subtracting), you can end up with unexpected results in
the vicinity of DST changes:
select '2007-03-12'::timestamptz - '1 day'::interval;
?column?
------------------------
2007-03-10 23:00:00-08
select '2007-03-12'::timestamp - '1 day'::interval;
?column?
---------------------
2007-03-11 00:00:00
Especially note that truncating a timestamptz preserves the timezone
info so you will very likely need to address issues on the days that
Daylight Saving starts or ends:
select date_trunc('day',current_timestamp);
date_trunc
------------------------
2007-06-07 00:00:00-07
Cheers,
Steve
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Re: subtract a day from the NOW function Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:58 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
@ 2007-06-07 19:22 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 21:07 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 19+ messages in thread
From: Michael Glaesemann @ 2007-06-07 19:22 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Campbell, Lance <lance@uiuc.edu>; pgsql-sql
On Jun 7, 2007, at 13:58 , Steve Crawford wrote:
> Beware in the "or something like that category" that PostgreSQL
> considers "1 day" to be "24 hours"
Actually, recent versions of PostgreSQL take into account daylight
saving time in accordance with the current PostgreSQL time zone
setting, so '1 day' in the context of timestamptz +/- interval may
be 23, 24, or 25 hours.
test=# select version();
version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5367)
(1 row)
test=# select '2007-03-12'::timestamptz, '2007-03-12'::timestamptz -
interval '1 day';
timestamptz | ?column?
------------------------+------------------------
2007-03-12 00:00:00-05 | 2007-03-11 00:00:00-06
(1 row)
test=# select '2007-11-04'::timestamptz, '2007-11-04'::timestamptz -
interval '1 day';
timestamptz | ?column?
------------------------+------------------------
2007-11-04 00:00:00-05 | 2007-11-03 00:00:00-05
(1 row)
test=# select '2007-11-04'::timestamptz, '2007-11-04'::timestamptz +
interval '1 day';
timestamptz | ?column?
------------------------+------------------------
2007-11-04 00:00:00-05 | 2007-11-05 00:00:00-06
(1 row)
test=# show time zone;
TimeZone
------------
US/Central
(1 row)
Note how the UTC offset changes across the daylight saving time change.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Re: subtract a day from the NOW function Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:58 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 19:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
@ 2007-06-07 21:07 ` Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 21:58 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 22:08 ` Re: subtract a day from the NOW function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 19+ messages in thread
From: Steve Crawford @ 2007-06-07 21:07 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Campbell, Lance <lance@uiuc.edu>; pgsql-sql
Michael Glaesemann wrote:
>
> On Jun 7, 2007, at 13:58 , Steve Crawford wrote:
>
>> Beware in the "or something like that category" that PostgreSQL
>> considers "1 day" to be "24 hours"
>
> Actually, recent versions of PostgreSQL take into account daylight
> saving time in accordance with the current PostgreSQL time zone setting,
> so '1 day' in the context of timestamptz +/- interval may be 23, 24, or
> 25 hours....
Interesting - thanks. That's one more thing I need to check when
upgrading my server. If my reading is correct, there are some subtle
gotchas here.
If I go back and try on a 7.4 machine it appears that interval makes a
DST correction if the interval includes a unit of "month" or greater but
does not make a correction for "week" or "day" intervals.
On 8.2 I'm seeing an adjustment if the DST adjustment includes units of
"day" or greater (ie. '1 day' and '1 day 01:00' get adjusted but '24
hours' and '25 hours' do not).
But PG doesn't follow the same rules in subtracting timestamptz values
so operations involving timestamps and intervals are (sometimes) not
reversible:
select timestamptz '2007-11-05' - timestamptz '2007-11-04';
?column?
----------------
1 day 01:00:00
select timestamptz '2007-11-04' + interval '1 day 01:00:00';
?column?
------------------------
2007-11-05 01:00:00-08
Cheers,
Steve
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Re: subtract a day from the NOW function Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:58 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 19:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 21:07 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
@ 2007-06-07 21:58 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-08 03:31 ` Re: [SQL] subtract a day from the NOW function Kevin Hunter <hunteke@earlham.edu>
1 sibling, 1 reply; 19+ messages in thread
From: Michael Glaesemann @ 2007-06-07 21:58 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Campbell, Lance <lance@uiuc.edu>; pgsql-sql
On Jun 7, 2007, at 16:07 , Steve Crawford wrote:
> On 8.2 I'm seeing an adjustment if the DST adjustment includes
> units of
> "day" or greater (ie. '1 day' and '1 day 01:00' get adjusted but '24
> hours' and '25 hours' do not).
>
> But PG doesn't follow the same rules in subtracting timestamptz values
> so operations involving timestamps and intervals are (sometimes) not
> reversible:
Right. It's only for timestamptz +/i interval.
> select timestamptz '2007-11-05' - timestamptz '2007-11-04';
>
> ?column?
> ----------------
> 1 day 01:00:00
It is a bit tricky. Datetime math is inherently so.
> select timestamptz '2007-11-04' + interval '1 day 01:00:00';
> ?column?
> ------------------------
> 2007-11-05 01:00:00-08
What PostgreSQL is doing behind the scenes is incrementing the date
2007-11-04 ahead 1 day and 1 hour. It treats months (and years),
days, and time separately.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Re: subtract a day from the NOW function Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:58 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 19:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 21:07 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 21:58 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
@ 2007-06-08 03:31 ` Kevin Hunter <hunteke@earlham.edu>
2007-06-08 04:12 ` Re: [SQL] subtract a day from the NOW function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 19+ messages in thread
From: Kevin Hunter @ 2007-06-08 03:31 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Steve Crawford <scrawford@pinpointresearch.com>; Scott Marlowe <smarlowe@g2switchworks.com>; Lance Campbell <lance@uiuc.edu>; PostgreSQL General List <pgsql-general@postgresql.org>
At 5:57p -0400 on 07 Jun 2007, Michael Glaesemann wrote:
> It is a bit tricky. Datetime math is inherently so.
So one wonders why the whole world doesn't migrate to a single timezone.
There would be no more confusion between EST, CEST, GMT, +1100, etc.
The trade off, of course, would be that now you'd have to know the
"daylight" hours of a particular part of the world or business with whom
you wanted to interact, but the math sure would be easier all-round.
Kevin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Re: subtract a day from the NOW function Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:58 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 19:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 21:07 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 21:58 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-08 03:31 ` Re: [SQL] subtract a day from the NOW function Kevin Hunter <hunteke@earlham.edu>
@ 2007-06-08 04:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-08 04:45 ` Re: [SQL] subtract a day from the NOW function John Meyer <john.l.meyer@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Tom Lane @ 2007-06-08 04:12 UTC (permalink / raw)
To: Kevin Hunter <hunteke@earlham.edu>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; Steve Crawford <scrawford@pinpointresearch.com>; Scott Marlowe <smarlowe@g2switchworks.com>; Lance Campbell <lance@uiuc.edu>; PostgreSQL General List <pgsql-general@postgresql.org>
Kevin Hunter <hunteke@earlham.edu> writes:
> At 5:57p -0400 on 07 Jun 2007, Michael Glaesemann wrote:
>> It is a bit tricky. Datetime math is inherently so.
> So one wonders why the whole world doesn't migrate to a single timezone.
Or at least get rid of daylight savings, which has to be one of the
worst ideas of the last 200 years ...
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Re: subtract a day from the NOW function Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:58 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 19:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 21:07 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 21:58 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-08 03:31 ` Re: [SQL] subtract a day from the NOW function Kevin Hunter <hunteke@earlham.edu>
2007-06-08 04:12 ` Re: [SQL] subtract a day from the NOW function Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-06-08 04:45 ` John Meyer <john.l.meyer@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: John Meyer @ 2007-06-08 04:45 UTC (permalink / raw)
To: PostgreSQL General List <pgsql-general@postgresql.org>
Tom Lane wrote:
> Kevin Hunter <hunteke@earlham.edu> writes:
>
>> At 5:57p -0400 on 07 Jun 2007, Michael Glaesemann wrote:
>>
>>> It is a bit tricky. Datetime math is inherently so.
>>>
>
>
>> So one wonders why the whole world doesn't migrate to a single timezone.
>>
>
> Or at least get rid of daylight savings, which has to be one of the
> worst ideas of the last 200 years ...
>
You mean we don't have to worry about defeating Jerry and the Kaiser
anymore? Boggles the mind.
Seriously, as long as everybody's in agreement, stick to UTC and let the
local software developers worry about formatting conversions to and from
for display.
--
The NCP Revue -- http://www.ncprevue.com/blog
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Re: subtract a day from the NOW function Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:58 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 19:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 21:07 ` Re: subtract a day from the NOW function Steve Crawford <scrawford@pinpointresearch.com>
@ 2007-06-07 22:08 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 19+ messages in thread
From: Tom Lane @ 2007-06-07 22:08 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; Scott Marlowe <smarlowe@g2switchworks.com>; Campbell, Lance <lance@uiuc.edu>; pgsql-sql
Steve Crawford <scrawford@pinpointresearch.com> writes:
> But PG doesn't follow the same rules in subtracting timestamptz values
> so operations involving timestamps and intervals are (sometimes) not
> reversible:
Yeah. timestamp_mi is performing a justify_hours call, which it should
not, but removing that call changes a lot of the regression test
outputs. So we've been afraid to change it. You can find more about
that in the archives.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
@ 2007-06-07 18:22 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 20:38 ` Re: subtract a day from the NOW function Fernando Hevia <fhevia@ip-tel.com.ar>
1 sibling, 1 reply; 19+ messages in thread
From: Michael Glaesemann @ 2007-06-07 18:22 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-sql
[Please don't top-post. It makes the discussion difficult to follow.]
On Jun 7, 2007, at 12:49 , Campbell, Lance wrote:
> 1) I have a timestamp field, "some_timestamp", in table "some_table".
> 2) I want to compare field "some_timestamp" to the current date - 1
> day.
> I need to ignore hours, minutes and seconds.
>
> Possible options:
>
> A) SELECT * FROM some_table WHERE some_timestamp::date >
> (CURRENT_DATE -
> INTERVAL '1 day')::date
Casting to date as you are will work. You can also use date_trunc:
SELECT *
FROM some_table
WHERE date_trunc('day', some_timestamp) > date_trunc('day',
(CURRENT_DATE - INTERVAL '1 day'));
Note the differences in the results:
SELECT CURRENT_TIMESTAMP, date_trunc('day', CURRENT_TIMESTAMP),
CURRENT_TIMESTAMP::date, CURRENT_DATE;
now | date_trunc | now
| date
-------------------------------+------------------------+------------
+------------
2007-06-07 13:21:28.186958-05 | 2007-06-07 00:00:00-05 | 2007-06-07 |
2007-06-07
date_trunc will return a timestamp.
> B) SELECT * FROM some_table WHERE to_char(some_timestamp,
> 'YYYYMMDD') >
> to_char((now() - interval '1 day'), 'YYYYMMDD');
I'd never use to_char to compare dates. The built-in comparison
operators work just fine.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
@ 2007-06-07 20:38 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-06-07 21:53 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 19+ messages in thread
From: Fernando Hevia @ 2007-06-07 20:38 UTC (permalink / raw)
To: 'Michael Glaesemann' <grzm@seespotcode.net>; +Cc: pgsql-sql
>> B) SELECT * FROM some_table WHERE to_char(some_timestamp,
>> 'YYYYMMDD') >
>> to_char((now() - interval '1 day'), 'YYYYMMDD');
>
>I'd never use to_char to compare dates. The built-in comparison
>operators work just fine.
>
Why not? I'm curious if has anything to do with performance or just style?
Any difference between:
... WHERE to_char(my_date_col:date, 'YYYY.MM.DD') < '2007.06.07'
and
... WHERE my_date_col:date < '2007.06.07'
Is there a 3rd better way to do this comparison?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:22 ` Re: subtract a day from the NOW function Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 20:38 ` Re: subtract a day from the NOW function Fernando Hevia <fhevia@ip-tel.com.ar>
@ 2007-06-07 21:53 ` Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 19+ messages in thread
From: Michael Glaesemann @ 2007-06-07 21:53 UTC (permalink / raw)
To: Fernando Hevia <fhevia@ip-tel.com.ar>; +Cc: pgsql-sql
On Jun 7, 2007, at 15:38 , Fernando Hevia wrote:
> Why not? I'm curious if has anything to do with performance or just
> style?
Not style. Maybe performance because there's fewer function calls,
but primarily correctness. By using to_char you no longer have a date—
you have a text value—and are relying on the collocation of your
database to compare two text values. This can lead to subtle bugs in
your code. Similarly, I would never use to_char to compare two integers:
SELECT 20 > 9 AS int_values
, to_char(20, '9') > to_char(9, '9') AS text_values;
Is this what you would expect? What's the advantage to using to_char?
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
@ 2007-06-07 21:41 ` Osvaldo Kussama <osvaldo_kussama@yahoo.com.br>
2007-06-08 14:15 ` Re: subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
1 sibling, 1 reply; 19+ messages in thread
From: Osvaldo Kussama @ 2007-06-07 21:41 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-sql
--- "Campbell, Lance" <lance@uiuc.edu> escreveu:
> Table
>
> Field "some_timestamp" is a timestamp.
>
>
>
> In a "WHERE" statement I need to compare a timestamp
> field in a table
> "some_timestamp" to now() - one day.
>
>
>
> Example:
>
>
>
> SELECT some_timestamp WHERE to_char(some_timestamp,
> 'YYYYMMDD') >
> (to_char(now(), 'YYYYMMDD') - 1 day);
>
>
>
> The statement "to_char(now(), 'YYYYMMDD') - 1 day)"
> is obviously
> incorrect. I just need to know how to form this in
> a way that will
> work.
>
>
>
> If there is an entirely different solution I am all
> for it. Do note
> that I started down this path because I want to
> exclude the hour,
> minutes and seconds found in the field
> "some_timestamp" and in the
> function now().
>
Try:
SELECT some_timestamp
WHERE some_timestamp > 'yesterday'::timestamp;
Look 8.5.1.5. Special Values at:
http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html
[]s
Osvaldo
____________________________________________________________________________________
Novo Yahoo! Cadê? - Experimente uma nova busca.
http://yahoo.com.br/oqueeuganhocomisso
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: subtract a day from the NOW function
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 21:41 ` Re: subtract a day from the NOW function Osvaldo Kussama <osvaldo_kussama@yahoo.com.br>
@ 2007-06-08 14:15 ` Campbell, Lance <lance@uiuc.edu>
0 siblings, 0 replies; 19+ messages in thread
From: Campbell, Lance @ 2007-06-08 14:15 UTC (permalink / raw)
To: Osvaldo Kussama <osvaldo_kussama@yahoo.com.br>; +Cc: pgsql-sql
Osvaldo,
Thanks! This is a great solution. It definitely is very easy to read. I like to have my SQL as clean as my java code. I ended up using the following:
SELECT some_timestamp FROM some_table WHERE some_timestamp::date > 'yesterday'::date;
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
-----Original Message-----
From: Osvaldo Kussama [mailto:osvaldo_kussama@yahoo.com.br]
Sent: Thursday, June 07, 2007 4:41 PM
To: Campbell, Lance
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] subtract a day from the NOW function
--- "Campbell, Lance" <lance@uiuc.edu> escreveu:
> Table
>
> Field "some_timestamp" is a timestamp.
>
>
>
> In a "WHERE" statement I need to compare a timestamp
> field in a table
> "some_timestamp" to now() - one day.
>
>
>
> Example:
>
>
>
> SELECT some_timestamp WHERE to_char(some_timestamp,
> 'YYYYMMDD') >
> (to_char(now(), 'YYYYMMDD') - 1 day);
>
>
>
> The statement "to_char(now(), 'YYYYMMDD') - 1 day)"
> is obviously
> incorrect. I just need to know how to form this in
> a way that will
> work.
>
>
>
> If there is an entirely different solution I am all
> for it. Do note
> that I started down this path because I want to
> exclude the hour,
> minutes and seconds found in the field
> "some_timestamp" and in the
> function now().
>
Try:
SELECT some_timestamp
WHERE some_timestamp > 'yesterday'::timestamp;
Look 8.5.1.5. Special Values at:
http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html
[]s
Osvaldo
____________________________________________________________________________________
Novo Yahoo! Cadê? - Experimente uma nova busca.
http://yahoo.com.br/oqueeuganhocomisso
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2007-06-08 14:15 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-07 16:08 subtract a day from the NOW function Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:36 ` Campbell, Lance <lance@uiuc.edu>
2007-06-07 16:39 ` Campbell, Lance <lance@uiuc.edu>
2007-06-07 17:27 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 17:49 ` Campbell, Lance <lance@uiuc.edu>
2007-06-07 18:12 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-06-07 18:58 ` Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 19:22 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 21:07 ` Steve Crawford <scrawford@pinpointresearch.com>
2007-06-07 21:58 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-08 03:31 ` Kevin Hunter <hunteke@earlham.edu>
2007-06-08 04:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-08 04:45 ` John Meyer <john.l.meyer@gmail.com>
2007-06-07 22:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-07 18:22 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 20:38 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-06-07 21:53 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-07 21:41 ` Osvaldo Kussama <osvaldo_kussama@yahoo.com.br>
2007-06-08 14:15 ` Campbell, Lance <lance@uiuc.edu>
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