2010/8/30 Yosef Haas <yosef@xxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxxx > > -----Original Message----- > From: Pavel Stehule [mailto:pavel.stehule@xxxxxxxxx] > Sent: Monday, August 30, 2010 4:45 PM > To: Yosef Haas > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: select query on Dates stored as varchar > > 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
