Thread: subtract a day from the NOW function

subtract a day from the NOW function

From

"Campbell, Lance"

Date:

07 June 2007, 16:31:11

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Table</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Field “some_timestamp” is a timestamp.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">In a “WHERE” statement I need to compare a timestamp field in a table “some_timestamp” to now() –
oneday.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Example:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">SELECT some_timestamp WHERE to_char(some_timestamp, ‘YYYYMMDD’) > (to_char(now(), ‘YYYYMMDD’) – 1
day);</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The statement “to_char(now(), ‘YYYYMMDD’) – 1 day)” is obviously incorrect.  I just need to know how
toform this in a way that will work.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">If there is an entirely different solution I am all for it.  Do note that I started down this path
becauseI want to exclude the hour, minutes and seconds found in the field “some_timestamp” and in the function
now().</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Lance Campbell</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Project Manager/Software Architect</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">Web Services at Public Affairs</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">University</span></font><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">of Illinois</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">217.333.0382</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">http://webservices.uiuc.edu</span></font><p class="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt"> </span></font></div>

Re: subtract a day from the NOW function

From

"Campbell, Lance"

Date:

07 June 2007, 16:35:38

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

Re: subtract a day from the NOW function

From

"Campbell, Lance"

Date:

07 June 2007, 16:38:17

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

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.

Thanks,

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

Re: subtract a day from the NOW function

From

Michael Glaesemann

Date:

07 June 2007, 17:27:18

> 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

Re: subtract a day from the NOW function

From

"Campbell, Lance"

Date:

07 June 2007, 17:48:29

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

Re: subtract a day from the NOW function

From

Scott Marlowe

Date:

07 June 2007, 18:12:10

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.

Re: subtract a day from the NOW function

From

Michael Glaesemann

Date:

07 June 2007, 18:22:15

[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

Re: subtract a day from the NOW function

From

Steve Crawford

Date:

07 June 2007, 18:58:47

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

Re: subtract a day from the NOW function

From

Michael Glaesemann

Date:

07 June 2007, 19:23:06

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

Re: subtract a day from the NOW function

From

"Fernando Hevia"

Date:

07 June 2007, 20:38:43

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

Re: subtract a day from the NOW function

From

Steve Crawford

Date:

07 June 2007, 21:07:41

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

Re: subtract a day from the NOW function

From

Osvaldo Kussama

Date:

07 June 2007, 21:48:11

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

Re: subtract a day from the NOW function

From

Michael Glaesemann

Date:

07 June 2007, 21:53:20

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

Re: subtract a day from the NOW function

From

Michael Glaesemann

Date:

07 June 2007, 21:58:15

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

Re: subtract a day from the NOW function

From

Tom Lane

Date:

07 June 2007, 22:08:28

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

Re: subtract a day from the NOW function

From

"Campbell, Lance"

Date:

08 June 2007, 14:14:27

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