Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

DBI & date/timestamp problem

by BigGuy (Friar)
on Jan 26, 2006 at 16:02 UTC ( #525752=perlquestion: print w/replies, xml ) Need Help??

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

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?

Re: DBI & date/timestamp problem
by VSarkiss (Monsignor) on Jan 26, 2006 at 16:10 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
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
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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://525752]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (3)
As of 2021-03-08 00:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favorite kind of desktop background is:











    Results (123 votes). Check out past polls.

    Notices?