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

Hello,

I have a problem when fetching, especially when displaying date/time format received by DBD-InterBase driver in PERL. I have a few Perl scripts that generate different HTML documents - from a XML file and some InterBase data. Everything worked fine on Windows (with DBD-InterBase-0.40), I was getting (by query:
SELECT TIME, VALUE, ID FROM TABLE1 WHERE (TIME > '1.5.2004 0:0:0' AND TIME < '20.5.2004 23:59:59'))
and displaying date/time format in my HTML pages like:
"15.5.2004 12:00:30" for fifteenth of May, year 2004 12:00PM (and 30 seconds).

But when I installed DBD-InterBase driver (version -0.40, -0.42 and -0.43) on Unix (Linux SuSe ver2.4.21) the date/time format I was getting & displaying was:
"Tue May 15, 12:00:30 2004" instead of "15.5.2004 12:00:30" (like on Windows).

After that, I replaced the DBD-InterBase driver on both the Windows and Unix, and now I have DBD-InterBase-0.43 on both systems! But, still I'm getting date/time format like:
- on Linux: "Tue May 15, 12:00:30 2004", and
- on Windows: "05/15/04 12:00:30" (previously it was "15.5.2004 12:00:30")!
I noticed (it was obvious) that date/time format is dependable of the DBD-InterBase driver, but still I cannot get my desired format, neither on Windows nor on Linux! (desired format should be like: "15.5.2004 12:00:30"). So, what should I do, or change to receive and display "my desired format" of date/time?

Anyone, any ideas, hints, suggestions on this?

I appreciate any help. Thanks.

Replies are listed 'Best First'.
Re: DBD-InterBase: different date/time formats
by pgor (Beadle) on Jun 22, 2004 at 17:18 UTC
    The DBD::Interbase::FAQ perldoc has a section on "How can I use the date/time formatting attributes?" To summarize, you can specify the format as an attribute when preparing the statement. 
    my $sth = $dbh->prepare( $query, { ib_timeformat => '%e.%m.%G %H:%M:%S
+' } )

    [download]
    should get you pretty close to what you want; experiment with the format string as you wish. (I've done plenty of DBI coding, but haven't used DBD::Interbase yet.)
[reply]
[d/l]
Re: DBD-InterBase: different date/time formats
by paulbort (Hermit) on Jun 22, 2004 at 17:18 UTC
    I haven't worked with InterBase specifically, but my guess is that DBD::Interbase is now paying attention to the locale that it is in, and trying to provide the data in a format appropriate to that locale.

    You can test this on the Windows box by telling it you're in Europe (Control Panel/Settings/Regional and Language Options on XP), and running the same program again. I expect you'll get a date with the month and day transposed, or with the year first.

    If that happens, then you just need to determine how DBD::Interbase determines the locale, and how you can set the machines to the same locale, or override the system setting.

    Another option would be to change the query to do the formatting for you. For example, the PostgreSQL to_char() function would allow you to specify the output format in the query, so that both clients would get a string in the exact same format. Simply converting the date to a string as part of the query might be enough to get it into a consistent format, too.

    --
    Spring: Forces, Coiled Again!
[reply]

      Hi everybody,

      with your help (thanks to paulbort, pgor, digger) I "figure it" out - found the solution, like previously mentioned monks said/posted to my question!

      So, I noticed that my so called problem was because of "undefined" date/time format of the DBD-InterBase driver, but after changing (or should I say adding) this before the prepare method, solved the problem: 

           . . .
    #desired format: day.month.year Hour:Minutes:Seconds
    $dbh->{ib_timestampformat} = '%d.%m.%Y %H:%M:%S';
    $sth = $dbh->prepare($query);
     . . .

      [download]
      or even (what is the same!): 
           . . .
    $attr = { ib_timestampformat => '%d.%m.%Y %H:%M:%S' };
    $sth = $dbh->prepare($query, $attr);
     . . .

      [download]

      Thanx again to all of you!

      Regards, Pet.

[reply]
[d/l]
[select]
Re: DBD-InterBase: different date/time formats
by digger (Friar) on Jun 22, 2004 at 17:23 UTC
    I don't have Firebird or Interbase available for testing, but according to the docs on CPAN , it looks like you can do this: $dbh->{ib_timestampformat} = '%m.%d.%Y %H:%M:%S'; to set the timestamp to the format you need. Any time you use $dbh, your time will be the same. If you are working with just the date format, the similar should work, modified according to the docs to set the Date format.

    Unfortunately, since I don't have a compatible db to work with, I wasn't able to test this in a production environment.

    HTH,
    digger
[reply]
[d/l]
Re: DBD-InterBase: different date/time formats
by pet (Novice) on Jun 23, 2004 at 08:09 UTC

    Hi everybody,

    with your help (thanks to paulbort, pgor, digger) I "figure it" out - found the solution, like previously mentioned monks said/posted to my question!

    So, I noticed that my so called problem was because of "undefined" date/time format of the DBD-InterBase driver, but after changing (or should I say adding) this before the prepare method, solved the problem: 

         . . .
    #desired format: day.month.year Hour:Minutes:Seconds
    $dbh->{ib_timestampformat} = '%d.%m.%Y %H:%M:%S';
    $sth = $dbh->prepare($query);
     . . .

    [download]
    or even (what is the same!): 
         . . .
    $attr = { ib_timestampformat => '%d.%m.%Y %H:%M:%S' };
    $sth = $dbh->prepare($query, $attr);
     . . .

    [download]

    Thanx again to all of you!

    Regards, Pet.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom