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

I could not think of a good title, because of what I'm trying to do.

I have a database that keeps a date, in time() format. Is there a way to search for dates that are on CERTAIN DAYS, like January 1, 2002, or something like that? I have been trying to figure it out, but cannot. I'd appreciate any tips/advice on the matter.

thx,
Richard

Replies are listed 'Best First'.
Re: time() searching
by BazB (Priest) on Jun 22, 2003 at 20:24 UTC

    • Quick, not very tidy and probably quite slow for many calls for different dates:

      • Figure out the time for Jan 1 2002, 0000hrs 0 sec and Jan 2 2002 0000hrs 0 sec and then check to see if the database time is between that.

    • Properly:

      • Hit CPAN, grab Date::Calc and knock together a script using that module
      • If it's a proper database (i.e. an RDBMS, not some CSV files), use the power of the database to do all this for you.

    Update: And if possible, it's probably a good idea to insert dates into the database using the database's native representation of date/time - then all this stuff can be done in the database.
    If it's something that can't be done in the DB, it's easy to reformat the date/times as you extract the data, because it's being stored in a way the DB understands.

    If the information in this post is inaccurate, or just plain wrong, don't just downvote - please post explaining what's wrong.
    That way everyone learns.

[reply]
Re: time() searching
by ctilmes (Vicar) on Jun 22, 2003 at 20:28 UTC
    Well, best (most flexible) is to keep your database with a real database native time format, but if you've got things in time() format (by which I think you mean "unix epoch time"), just convert the date you want to search for into that same format and search for it. You can use Time::Local: 
    my $startdate = timelocal(0,0,0,1,0,2002-1900); # Jan 1
my $enddate = timelocal(0,0,0,2,0,2002-1900);   # Jan 2

    [download]
    then search for dates between those.

    Curt

[reply]
[d/l]
      Agreed.

      If you keep a "readable" date/time stamp in the database, then you pay the penalty to convert from time() to a real date/time stamp once ... when you create or update the record.

      If you have the time() format in your database, then you likely have to pay the price to convert it to readable date/time every time you access the record.

[reply]
Re: time() searching
by DrHyde (Prior) on Jun 23, 2003 at 09:36 UTC
    If you're using a SQL-ish database, look at the BETWEEN keyword or your database's date manipulation functions. Otherwise, you can easily derive the start of day and end of day epoch times from an epoch time. You just need to remeber that epoch time 0 is midnight in UTC and that there are 86400 seconds in a day. Therefore, any multiple of 86400 is midnight UTC: 
    my $timestamp = 1056360394;
my $startofday = $timestamp - ($timestamp % 86400);
my $endofday = $startofday + 86400;

    [download]
    Apply offsets as appropriate for your timezone. Figuring out whether a given epoch time is between $startofday and $endofday is trivial.
[reply]
[d/l]

Back to Seekers of Perl Wisdom