[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, 'MMDD') > (to_char(now(), 'MMDD') - 1 day); The statement "to_char(now(), 'MMDD') - 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
Re: [SQL] subtract a day from the NOW function
I just figured it out. The solution is: select to_char((now() - interval '1 day'), 'MMDD'); Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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, 'MMDD') > (to_char(now(), 'MMDD') - 1 day); The statement "to_char(now(), 'MMDD') - 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
Re: [SQL] subtract a day from the NOW function
Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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'), 'MMDD'); Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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, 'MMDD') > (to_char(now(), 'MMDD') - 1 day); The statement "to_char(now(), 'MMDD') - 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
Re: [SQL] subtract a day from the NOW function
From: [EMAIL PROTECTED] [mailto:pgsql-sql- [EMAIL PROTECTED] 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, ‘MMDD’) > (to_char(now(), ‘MMDD’) – 1 day); On Jun 7, 2007, at 11:36 , Campbell, Lance wrote: select to_char((now() - interval '1 day'), 'MMDD'); 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] subtract a day from the NOW function
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, 'MMDD') > to_char((now() - interval '1 day'), 'MMDD'); 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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] 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, 'MMDD') > > (to_char(now(), 'MMDD') - 1 day); On Jun 7, 2007, at 11:36 , Campbell, Lance wrote: > select to_char((now() - interval '1 day'), 'MMDD'); 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] subtract a day from the NOW function
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.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] subtract a day from the NOW function
[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,
'MMDD') >
to_char((now() - interval '1 day'), 'MMDD');
I'd never use to_char to compare dates. The built-in comparison
operators work just fine.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] subtract a day from the NOW function
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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] subtract a day from the NOW function
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] subtract a day from the NOW function
>> B) SELECT * FROM some_table WHERE to_char(some_timestamp, >> 'MMDD') > >> to_char((now() - interval '1 day'), 'MMDD'); > >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, '.MM.DD') < '2007.06.07' and ... WHERE my_date_col:date < '2007.06.07' Is there a 3rd better way to do this comparison? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] subtract a day from the NOW function
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] subtract a day from the NOW function
--- "Campbell, Lance" <[EMAIL PROTECTED]> 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, > 'MMDD') > > (to_char(now(), 'MMDD') - 1 day); > > > > The statement "to_char(now(), 'MMDD') - 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] subtract a day from the NOW function
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] subtract a day from the NOW function
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] subtract a day from the NOW function
Steve Crawford <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [ADMIN] the right time to vacuum database?
What's in your server? Osmar Della Paschoa Jr Software Engineer ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
