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

Hey,

I'm trying to save the time that a message is posted into an access da
+tabase and for some reason i'm getting a number like 18 instead of 12
+/4/2000 3:45pm.

I'm simple using Localtime();

Thanks,
Kiko

[download]

Replies are listed 'Best First'.
(tye)Re: Database time or just time
by tye (Sage) on Dec 05, 2000 at 06:55 UTC

    It sounds like you are calling localtime() in a list context and just storing the first value returned (the seconds) into the database.

    Without seeing your code, I'd suggest using ''.localtime() so that you are sure to get the string version of the return value.

    Update: Um, I don't do much with dates in databases these days, but I think you'll need to format the date so that your database will recognize it. Extracting the fields as Maclir suggests and then using sprintf is probably a better idea. Don't forget to add 1900 to the year and 1 to the month, though!

            - tye (but my friends call me "Tye")
[reply]
[d/l]
Re: Database time or just time
by Maclir (Curate) on Dec 05, 2000 at 06:57 UTC
    localtime() returns a 9 element list. It is typically used as follows: 
    ($sec,$min,$hour,$mday,$mon,$year,$wday,$year,$isdst) = localtime(time
+);

    [download]
    (Page 185 of "Programming Perl", 2nd ed.)
[reply]
[d/l]
Re: Database time or just time
by chipmunk (Parson) on Dec 05, 2000 at 07:14 UTC
    I know that Oracle has a special value called SysDate that returns the current date and time.
    In Oracle, you could do: 
    INSERT INTO mytable (user_id, subject, message, message_date)
             VALUES ('Bob', 'Lunch', 'Anyone want lunch?', SysDate)

    [download]

    Before you write code to format the date in Perl, you might check whether Access has something similar.

[reply]
[d/l]
      The function you are looking for in Access would simply be 'Now()' 
      INSERT INTO mytable (user_id, subject, message, message_date)
      VALUES ('Bob', 'Lunch', 'Anyone want lunch?', NOW())

      [download]
      --Glenn
[reply]
[d/l]
Re: Database time or just time
by clemburg (Curate) on Dec 05, 2000 at 14:23 UTC

    When dealing with dates in databases (and generally, too), you will profit a lot from using the representation commonly known as ISO Date Format, described in more detail in ISO 8601:1988 Date/Time Representations (Note that this is only a description of the standard, not the standard itself (for which the copyright is held by the ISO)), which is commonly used in the two forms 'YYYYMMDD' and 'YYYY-MM-DD', where Y is a year digit, M is a month digit, and D is a day digit. Access will recognize and autoconvert the first form without problems, as will MS SQL Server and Sybase.

    This form of representation has numerous advantages, not the least being that you can sort dates represented like that by a simple string comparison sort.

    Much more nice info on dates is available in a very compact form at J. R. Stockton's Date Formats page.

    Christian Lemburg
    Brainbench MVP for Perl
    http://www.brainbench.com

[reply]

Back to Seekers of Perl Wisdom