From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Yosef Haas <yosef(at)karatedepot(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: select query on Dates stored as varchar |
Date: | 2010-08-30 21:23:06 |
Message-ID: | 1283203386.6656.10.camel@jdavis-ux.asterdata.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Yosef Haas | 2010-08-31 01:05:58 | Re: select query on Dates stored as varchar |
Previous Message | Carlos Henrique Reimer | 2010-08-30 21:20:54 | Re: Restore referencial integrity |