Search Postgresql Archives

Re: select query on Dates stored as varchar

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello

2010/8/30 Yosef Haas <yosef@xxxxxxxxxxxxxxx>:
> 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@xxxxxxxxxxxxxxx
>
>

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux