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@xxxxxxxxxxxxxxx -----Original Message----- From: Jeff Davis [mailto:pgsql@xxxxxxxxxxx] Sent: Monday, August 30, 2010 5:23 PM To: Yosef Haas Cc: pgsql-general@xxxxxxxxxxxxxx Subject: 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
