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

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
[mailtogsql-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

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
[mailtogsql-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
[mailtogsql-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

> From: pgsql-sql-owner@postgresql.org [mailtogsql-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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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 [mailtogsql-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



---------------------------(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

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

[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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>> 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?



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly