How can I store a timestamp in a DBM database?

Question

I am implemening simple file-sharing service. Currently I use a file-based database (as it suits my needs).

   # somewhere in my cgi script
   sub first_run
    {
      my $dbh = DBI->connect('dbi:DBM:');
      $dbh = DBI->connect("dbi:DBM:", "", "",{ AutoCommit => 1, RaiseError => 1, f_dir=>$DATABASE_DIR} );
      unless ($dbh)
      {
        print "<br>Cannot connect: $DBI::errstr";
        return undef;
      }
      $dbh->do("CREATE TABLE uploads( user_name TEXT,
        file_name TEXT, 
        upload_date TEXT ) ");
      $dbh->disconnect( );
    }

As you can see I propose to store the upload timestamp as a string, since currently I only have to display it (suppose to use localtime() to get timestamp in a human readable format). But this seems to me somewhat bad. What if later I'll want to show uploads from some period, etc.

What is the common way of storing timestamps in a DBM database without using third-party, CPAN modules? How can I pull them later and show them to the user (in my situation, convert back to string)?

Answer 1

Consider that storing UTC time is less ambiguous than storing local time. If you save a time at your computer in London and send me the file in Hong Kong, will I when I load the file in your program see the time you actually saved?

Consider that in storing UTC time you have (at least) two choices: the formatted time string, or a count of numbers of (seconds/milliseconds/whatever) since a particular time ("the epoch"). Presumably either format is isomorphic with the other, as there is a well-defined bi-directional conversion rom one to the other.

Answer 2

I would just store the epoch, it's easy to then display it with *localtime* or *strftime*.

If you really want to be able to browse the content of the DB without having to convert back the epoch, you could store both the numerical value and the *localtime* string (" `1238693517 - Thu Apr 2 19:31:57 2009`"). This makes the data redundant and denormalized, but also makes it easy to inspect, while still being able to process the numerical value. Your call.