DBI & date/timestamp problem

BigGuy has asked for the wisdom of the Perl Monks concerning the following question:

I have a small program that takes a flat file and dumps it to a postgres table via DBI. Previously it just inserted the dates as a text string, someone now wants the dates in a date or a timestamp field to improve sorting capabilities.

No big deal right? Somewhere along the line my date are being interpolated wrongly, or I could be feeding the data in wrong I'm not quite sure.

The date format in the flat file is yy-mm-dd
In my code I split it up, and rearrange it how postgres wants it (mm/dd/yyyy)
I've tried /'s and -'s as delimeters.

Example: raw date from file: 05-12-01
modified date as i insert it: 12/01/2005
date stored in table after insertion: 2001-05-12

Example 2: raw date from file: 06-01-25
modified date as i insert it: 01/25/2006
date stored in table after insertion: 2025-06-01

The behavior is exactly the same whether its a timestamp field or a date field.
This seems like such a trivial problem, but it has really thrown me for a loop. Any ideas what I am doing wrong?

BigGuy "One World, one Web, one Program" - Microsoft promotional ad
"Ein Volk, ein Reich, ein Fuhrer" - Adolf Hitler

Comment on DBI & date/timestamp problem

Replies are listed 'Best First'.
Re: DBI & date/timestamp problem by Yendor (Pilgrim) on Jan 26, 2006 at 16:38 UTC
Have you tried using the Postgres to_date function instead of just inserting it as a String which you hope will be interpreted correctly? -Yendor IWeThey	[reply]
Re: DBI & date/timestamp problem by VSarkiss (Monsignor) on Jan 26, 2006 at 16:10 UTC
I may be reading this wrong, but why are you re-formatting at all? Pg should handle the yyyy-mm-dd just fine. If anything, just prepend `20` to the beginning to make sure the year's interpreted correctly and it should be OK. Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l]
Re^2: DBI & date/timestamp problem by BigGuy (Friar) on Jan 26, 2006 at 16:20 UTC
I forgot to mention in my post that originally I did try feeding the unadulterated date, and tried prepending 20 on the year. With the same results. BigGuy "One World, one Web, one Program" - Microsoft promotional ad "Ein Volk, ein Reich, ein Fuhrer" - Adolf Hitler	[reply]
Re: DBI & date/timestamp problem by ptum (Priest) on Jan 26, 2006 at 17:37 UTC
So, most databases have a 'default' date format that is set by whoever installs your database. For example, our current default date format for an Oracle database I work with has a DD-MON-YY default format. In general, you should always use some variant of an explicit TO_DATE($string_date,$format) converting function so that you don't get any surprises (like when you submit 05/04/06 and end up with April 6, 2005 instead of May 4, 2006). No good deed goes unpunished. -- (attributed to) Oscar Wilde	[reply]
Re: DBI & date/timestamp problem by BigGuy (Friar) on Jan 26, 2006 at 17:39 UTC
Thanks for your help. Yendor's suggestion helped steer me in the right direction. All fixed. BigGuy "One World, one Web, one Program" - Microsoft promotional ad "Ein Volk, ein Reich, ein Fuhrer" - Adolf Hitler	[reply]