From: Steve Crawford <scrawford@pinpointresearch.com>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Campbell, Lance <lance@uiuc.edu>
Cc: Michael Glaesemann <grzm@seespotcode.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: subtract a day from the NOW function
Date: Thu, 07 Jun 2007 11:58:38 -0700
Message-ID: <4668555E.20003@pinpointresearch.com> (raw)
In-Reply-To: <46684A70.4080903@g2switchworks.com>
References: <A3AC4FA47DC0B1458C3E5396E685E63302395E24@SAB-DC1.sab.uiuc.edu>
<A3AC4FA47DC0B1458C3E5396E685E63302395E25@SAB-DC1.sab.uiuc.edu>
<9938A77A-6E63-413D-8AC9-7CB741D075E6@seespotcode.net>
<A3AC4FA47DC0B1458C3E5396E685E63302395E27@SAB-DC1.sab.uiuc.edu>
<46684A70.4080903@g2switchworks.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, smarlowe@g2switchworks.com, lance@uiuc.edu, grzm@seespotcode.net
Subject: Re: subtract a day from the NOW function
In-Reply-To: <4668555E.20003@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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