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

Oh wise ones,

I'm in need of your wisdom regarding timestamps.
At one point I was performing data collection from logs that contained timestamps as such (Mon Sep 15 08:00:32 2008) and converted them to be inserted into a database.
I used the mktime() function to accomplish this for example:

mktime( $sec, $min, $hour, $mday, ($mon-1), ($year-1900), $wd, $sd, -1 )-315964800;

Now I have a new set of logs that contains timestamps as such (09/10 17:18:46:126). My problem here is that mktime() does not handle miliseconds and I need to be able to convert back to original timestamp when retrieving the data from the database.
Any help would be appreciated.

Replies are listed 'Best First'.
Re: Timestamp help
by jethro (Monsignor) on Sep 12, 2008 at 21:13 UTC

    I guess you have put the time into an integer field, so you can't store the milliseconds as fractions of a second !?

    In that case you might store any new data as milliseconds instead of seconds from now on and differentiate the two formats by size (i.e any number greater than the maximal number of seconds you stored before the switch is automatically a millisecond

    You should check that the integer type of the database can get big enough to last for a few years (10 years are 307 billion milliseconds). If not you need to add a new field into your database or change the timestamp field to a real number (if that provides enough resolution)

[reply]
Re: Timestamp help
by LesleyB (Friar) on Sep 12, 2008 at 21:21 UTC

    While you don't mention which RDBMS system you will be using, I've recently ventured into the C API for MySQL and used the TIMESTAMP data type.

    From what I recall of it, it doesn't handle milliseconds either.

    If it is an absolute necessity to record those milliseconds, you could continue to use what you already have to convert the major part of that new time string but add a column to the relevant table to hold the milliseconds part. This new column could default to 0 for logs that don't contain millisecond information.

    One advantage might be that existing code for previous log formats won't need to be disturbed but you would need to check the value of this column when reconstructing the timestamp.

[reply]
Re: Timestamp help
by runrig (Abbot) on Sep 12, 2008 at 21:44 UTC
    Create an actual DATETIME or TIMESTAMP column that will handle milliseconds. Update the new column to contain the converted values from your current integer column. Then just start using your new column to store the timestamps.
[reply]
Re: Timestamp help
by Illuminatus (Curate) on Sep 12, 2008 at 21:28 UTC
    mktime returns a standard unix timestamp (seconds since 1/1/1970). Note the word 'seconds'. I think your best option is to add a new column to hold microseconds (best to plan for the future :), setting the default value to 0. That way, all existing rows will be set correctly.
[reply]
Re: Timestamp help
by jvector (Friar) on Sep 13, 2008 at 16:50 UTC
    If you read your logfile lines and pass them through an appropriate regexp you should be able to extract the individual components; you can then pass them in a call to DateTime->new . From the DateTime man page: 
                 my $dt = DateTime->new( year   => 1066,
                                     month  => 10,
                                     day    => 25,
                                     hour   => 7,
                                     minute => 15,
                                     second => 47,
                                     nanosecond => 500000000,
                                     time_zone  => ’America/Chicago’,
                                   );

    [download]

    I'm not sure if that will help you - depends on what you need to do. But DateTime is pretty flexible.

[reply]
[d/l]
Re: Timestamp help
by pajout (Curate) on Sep 13, 2008 at 19:28 UTC
    I recommend to explore rdbms built-in datetime types and functions. Perhaps you will find something like as StrToDateTime(pattern, string), which will allow to do something like as
    INSERT INTO mytable (mytimestamp) 
 VALUES (StrToDateTime("%month %day %h24:%mi:%ss.%xxx",?));

    [download]
    Attention: Pattern used in insert is not real.

    Or, you can reformat the timestamp in Perl code (as advised above) into rdbms native string format of timestamp - some rdbms automatically convert properly formatted string to timestamp.

    But, in both cases, your code will depend on rdbms capabilities.

[reply]
[d/l]
[reply]

Back to Seekers of Perl Wisdom