Re: 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