I agree wholeheartedly with all of your points. The data should be in the
right format. The problem I'm faced with is that I'm in the middle of a
hosting migration and can't stop to change and deploy all the applications
that expect the date in that format.
Pavel did however point me in the right direction to making this work the
way it did. The answer was the locale. Once I did initdb with
--locale=en_US.UTF-8 it worked. I know this is not great for the long run,
but right now I need to get this up and running.
Thanks for the help.
Yosef Haas
yosef@karatedepot.com
-----Original Message-----
From: Jeff Davis [mailto:pgsql@j-davis.com]
Sent: Monday, August 30, 2010 5:23 PM
To: Yosef Haas
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] select query on Dates stored as varchar
On Mon, 2010-08-30 at 16:32 -0400, Yosef Haas wrote:
> 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
>
>
...
>
> Any ideas? Is there somewhere that I can specify that with, or without
> the hyphens, they are both dates?
>
For now, write a function called something like my_string_to_timestamp()
that takes a string in the format above and converts it to a real
timestamp (or timestamptz). Then, use a query like:
select (*) from transactions
where my_string_to_timestamp(order_date) > '20100624'::timestamp;
That way, you at least have the ugly interpretation logic in one place
(the function), and you will see errors when you run into a malformed
string.
Later, you really should change these to be real timestamps (or
timestamptz). Trying to re-interpret your data at query time is the
recipe for wrong answers.
Regards,
Jeff Davis