Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to convert postgres timestamp to date: yyyy-mm-dd

How to convert postgres timestamp to date: yyyy-mm-dd

From
CaseT
Date:
Hi All,

I'm a novice but learning quickly and I'm stumped on how to do this.

I need to convert postgres timestamp to date format yyyy-mm-dd in a
sql statement.
pt.created_date below is timestamp format

i.e ... WHERE pt.created_date >=  '2008-01-21'

Any help would be greatly appreciated.

Thanks, Case

Re: How to convert postgres timestamp to date: yyyy-mm-dd

From
"Adam Rich"
Date:
> I need to convert postgres timestamp to date format yyyy-mm-dd in a
> sql statement.
> pt.created_date below is timestamp format
>
> i.e ... WHERE pt.created_date >=  '2008-01-21'
>
> Any help would be greatly appreciated.

Try this:

WHERE pt.created_date >= '2008-01-21'::date

Re: How to convert postgres timestamp to date: yyyy-mm-dd

From
"A. Kretschmer"
Date:
am  Tue, dem 11.03.2008, um 10:51:21 -0700 mailte CaseT folgendes:
> Hi All,
>
> I'm a novice but learning quickly and I'm stumped on how to do this.
>
> I need to convert postgres timestamp to date format yyyy-mm-dd in a
> sql statement.
> pt.created_date below is timestamp format
>
> i.e ... WHERE pt.created_date >=  '2008-01-21'

You can't compare a date or timestamp to a varchar or text. For your
example, cast the date-string to a real date like:

  ... WHERE pt.created_date >=  '2008-01-21'::date


Consider also functions like to_date(), see:
http://www.postgresql.org/docs/current/static/functions-formatting.html


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: How to convert postgres timestamp to date: yyyy-mm-dd

From
Vivek Khera
Date:
On Mar 11, 2008, at 2:50 PM, A. Kretschmer wrote:

>> i.e ... WHERE pt.created_date >=  '2008-01-21'
>
> You can't compare a date or timestamp to a varchar or text. For your
> example, cast the date-string to a real date like:

Since which version of Pg?

Queries like the above have worked for me from 7.3 up thru 8.1, which
is my current production environment.

Re: How to convert postgres timestamp to date: yyyy-mm-dd

From
Tom Lane
Date:
Vivek Khera <vivek@khera.org> writes:
> On Mar 11, 2008, at 2:50 PM, A. Kretschmer wrote:
>>>> i.e ... WHERE pt.created_date >=  '2008-01-21'
>>
>> You can't compare a date or timestamp to a varchar or text. For your
>> example, cast the date-string to a real date like:

> Since which version of Pg?
> Queries like the above have worked for me from 7.3 up thru 8.1, which
> is my current production environment.

The above example is not in fact comparing to a varchar or text value.
It's comparing to an unknown-type literal constant, which will
preferentially be resolved as being the same type as the variable
it's being compared to.

Whether you can do a cross-data-type comparison between two variables of
known data types is a whole 'nother story.  PG 8.3 insists on an
explicit cast in some cases where earlier versions silently did a
(possibly surprising) type conversion.

            regards, tom lane

Re: How to convert postgres timestamp to date: yyyy-mm-dd

From
Thomas Kellerer
Date:
A. Kretschmer wrote on 11.03.2008 19:50:
> am  Tue, dem 11.03.2008, um 10:51:21 -0700 mailte CaseT folgendes:
>> Hi All,
>>
>> I'm a novice but learning quickly and I'm stumped on how to do this.
>>
>> I need to convert postgres timestamp to date format yyyy-mm-dd in a
>> sql statement.
>> pt.created_date below is timestamp format
>>
>> i.e ... WHERE pt.created_date >=  '2008-01-21'
>
> You can't compare a date or timestamp to a varchar or text. For your
> example, cast the date-string to a real date like:
>
>   ... WHERE pt.created_date >=  '2008-01-21'::date
>

You can also use the ANSI standard for specifying date literals:

WHERE pt.created_date >=  DATE '2008-01-21'

which will work with other (standard compliant) DBMS as well.

Thomas