Re: select query on Dates stored as varchar - Mailing list pgsql-general

I know it would be best to change the data type of the column (I didn't
initially create the table), but doing so would be require changes to
several application that use it and I'd like to avoid that if possible.

Thanks,
Yosef Haas
yosef@karatedepot.com

-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Monday, August 30, 2010 4:45 PM
To: Yosef Haas
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] select query on Dates stored as varchar

Hello

2010/8/30 Yosef Haas <yosef@karatedepot.com>:
> I have a table (transactions) with an order_date field that is
varchar(32).
> The data looks like this:
>
>
>
>     order_date
>
> -------------------
>
> 20100623 02:16:40
>
> 20100623 04:32:41
>
> 20100625 04:18:53
>
> 20100625 07:53:24
>
>
>
> In my current database (8.1.4) if I run
>
> select (*) from transactions where order_date > ‘2010-06-24’;
>
> I get:
>
> count
>
> --------
>
> 2
>
>
>
> I’m moving to a new server that has 8.2.11. There, if I run the same
query,
> I get
>
> count
>
> --------
>
> 4
>
>
>
>
>
> In both, select (*) from transactions where order_date > ‘20100624’;
returns
> 2.
>
>
>
> The newer version does not seem to know that ‘2010-06-24’ is a date the
same
> way that ‘20100624’ is.
>
>
>
> Any ideas? Is there somewhere that I can specify that with, or without the
> hyphens, they are both dates?
>

a) check your locales - different locales can make different order
b) use a native data type "timestamp" - your technique isn't best - it
is slower, needs more memory and depends on locale.

Regards

Pavel Stehule

>
>
>
>
> Thank you,
>
> Yosef Haas
>
> yosef@karatedepot.com
>
>