Skip site navigation (1) Skip section navigation (2)

Re: Query on DATETIME for a date (the whole day)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Basil Bourque <basil(dot)list(at)me(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Query on DATETIME for a date (the whole day)
Date: 2011-10-20 02:32:27
Message-ID: 21278.1319077947@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Basil Bourque <basil(dot)list(at)me(dot)com> writes:
> For a "timestamp with time zone" column, how do I find all rows where the value is within the beginning and ending of a single date?

If you compare a date to a timestamp, the date is taken to mean midnight
of its day.  So you need something along the line of

	WHERE timestampcol BETWEEN dateval AND dateval+1

> And I'm concerned about local date time. I want to find by the user's local beginning and end of the day, not UTC.

Well, you're not being too clear about what you need here, but in what
I suggest above, "midnight" will be interpreted according to the current
"timezone" setting.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Merlin MoncureDate: 2011-10-20 13:42:15
Subject: Re: Can triggers update other tables?
Previous:From: Basil BourqueDate: 2011-10-20 01:08:00
Subject: Query on DATETIME for a date (the whole day)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group