Re: 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

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(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
Sent: Thursday, June 07, 2007 11:09 AM
To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
Sent: Thursday, June 07, 2007 11:37 AM
To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
Sent: Thursday, June 07, 2007 11:09 AM
To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org [mailto:pgsql-sql-
> owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
> Sent: Thursday, June 07, 2007 11:09 AM
> To: pgsql-sql(at)postgresql(dot)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

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(at)seespotcode(dot)net]
Sent: Thursday, June 07, 2007 12:27 PM
To: Campbell, Lance
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] subtract a day from the NOW function

> From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-
> owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
> Sent: Thursday, June 07, 2007 11:09 AM
> To: pgsql-sql(at)postgresql(dot)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

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.

[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

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

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

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

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

--- "Campbell, Lance" <lance(at)uiuc(dot)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

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

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

Steve Crawford <scrawford(at)pinpointresearch(dot)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

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

Kevin Hunter <hunteke(at)earlham(dot)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

Tom Lane wrote:
> Kevin Hunter <hunteke(at)earlham(dot)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

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(at)yahoo(dot)com(dot)br]
Sent: Thursday, June 07, 2007 4:41 PM
To: Campbell, Lance
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] subtract a day from the NOW function

--- "Campbell, Lance" <lance(at)uiuc(dot)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