Re: select query on Dates stored as varchar

2010/8/30 Yosef Haas <yosef(at)karatedepot(dot)com>:
> 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.
>

then you can dynamically transform data to timestamp (it is slower,
but not depends on locale)

SELECT to_timestamp(order_date, ''YYYYMMDD HH:MI:SS') >
to_timestamp(‘2010-06-24’, ''YYYYMMDD HH:MI:SS')

Regards

Pavel Stehule

> Thanks,
> Yosef Haas
> yosef(at)karatedepot(dot)com
>
> -----Original Message-----
> From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
> Sent: Monday, August 30, 2010 4:45 PM
> To: Yosef Haas
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] select query on Dates stored as varchar
>
> Hello
>
> 2010/8/30 Yosef Haas <yosef(at)karatedepot(dot)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(at)karatedepot(dot)com
>>
>>
>
>