I wrote this subroutine to allow me to generate a MySQL style date for X number of days ago. The ideas is that we want to generate reports for, say, the last week. This lets me pass $days as an argument and get a formatted date (yyyy-mm-dd) returned so that I can use it in the MySQL query as a start date.
use Time::Local;
use Time::localtime;

#####################################
# number of days ago to begin report
#####################################

$days = 7;

# calculate the starting date
$start_date = &calcDate($days);

# calculate the ending date
# we use 0 which means current day
# or 0 days ago
$end_date = &calcDate(0);

###########################################################
# subroutine: calcDate
# this subroutine takes an integer as an argument
# which represents the number of days in the past
# that we want to start getting data from
###########################################################

sub calcDate {

   # assign the argument to variable
   my($offset) = @_;

   # calculate the number of epoch seconds
   # in the requested days
   my $epoch_offset = $offset * 86400;

   # get the current time & date
   my $date  = localtime;

   # place parts into variables
   my $mday    = $date->mday;
   my $mon     = $date->mon;
   my $year    = $date->year;

   # format dates
   $mon++;
   $year = $year + 1900;


   # get the seconds since epoch
   my $epoch = timelocal($seconds, $minutes, $hours, $mday, $mon, $yea
+r);

   # calculate the number of epoch seconds since requested days ago
   my $past_epoch = $epoch - $epoch_offset;

   # get the date of X (requested) days ago
   my $past_date = localtime($past_epoch);

   # place parts into variables
   my $past_mday    = $past_date->mday;
   my $past_mon     = $past_date->mon;
   my $past_year    = $past_date->year;

   # y2k compliance
   $past_year = $past_year + 1900;

   #format the date
   my $date = "$past_year-$past_mon-$past_mday";

   # return the formatted date
   return "$date";

} # end of subroutine

[download]

Replies are listed 'Best First'.
RE: Get the date (MySQL style) for X days ago
by httptech (Chaplain) on Apr 14, 2000 at 21:16 UTC 
    use Date::Manip;

print UnixDate("56 days ago", "%Y-%m-%d\n");

    [download]
[reply]
[d/l]
RE: Get the date (MySQL style) for X days ago
by btrott (Parson) on Apr 14, 2000 at 21:30 UTC
    Or, if you'd rather use POSIX: 
    my $offset = 65;
my $date = strftime "%Y-%m-%d",
    localtime(time - $offset * 86400);

    [download]
    And if you'd rather have the format for a datetime field: 
    my $datetime = strftime "%Y-%m-%d %H:%M:%S",
    localtime(time - $offset * 86400);

    [download]
    In fact, MySQL itself has some date/time functions that do a lot of the formatting for you, so you could use those instead. The FROM_UNIXTIME function takes a Unix timestamp (epoch seconds) and turns it into a MySQL datetime format for you: 
    my $offset = 65;
my $time = $offset * 86400;

my $sth = $dbh->prepare_cached(<<SQL);
select host
from log
where stamp > from_unixtime(?)
SQL
$sth->execute($time);

    [download]
    where "stamp" is a datetime field.

    Of course, if what the original poster really wants is a report of records from the last 7 days, all he/she'd have to use is something like this, which lets MySQL do all the work: 

    my $sth = $dbh->prepare_cached(<<SQL);
select host
from log
where to_days(now()) - to_days(stamp) > ?
SQL
$sth->execute(7);

    [download]
    where stamp is, again, a datetime field.

    And finally, one more way of doing this--this will give you slightly different results than the last query, because the last query is giving you basically anything from the last 7 days; the following will give you anything *within* the last 7 days (a subtle difference): 

    my $sth = $dbh->prepare_cached(<<SQL);
select host
from log
where stamp > date_sub(now(), interval ? day)
SQL
$sth->execute(7);

    [download]
[reply]
[d/l]
[select]
      Your first three solutions are wrong. You assume that every day has 86400 seconds. 
      use POSIX       qw( strftime );
use Time::Local qw( timelocal );

# Pretend we're executing at 12:00:05 am on Mar 12th, 2008.
# The DST time zone change occured on Mar 9th, 2008 here.
my $time = timelocal(5,0,0,12,3-1,2008);

my $offset = 7;
my $date = strftime "%Y-%m-%d",
    localtime($time - $offset * 86400);

print("Expecting: 2008-03-05\n");
print("Received:  $date\n");

      [download]
      Expecting: 2008-03-05
Received:  2008-03-04

      [download]

      See Re: Get the date (MySQL style) for X days ago for a solution.

[reply]
[d/l]
[select]
RE: Get the date (MySQL style) for X days ago
by Anonymous Monk on Feb 17, 2000 at 10:25 UTC
    Perhaps I'm missing something, but you're doing way too many calculations in there. You're taking epoch seconds, computing the date in month, day, and year, then converting back to epoch seconds? In addition, you get a warning: 
    # "my" variable $date masks earlier declaration in same scope.

    [download]
    You declared $date twice as a my variable. Try this: 
    sub calcDate {
    my $offset = shift;
    my($mday, $mon, $year) =
        (localtime(time - $offset * 86400))[3..5];
    return sprintf "%04s-%02s-%02s", $year + 1900,
        $mon + 1, $mday;
}

    [download]
[reply]
[d/l]
[select]
Re: Get the date (MySQL style) for X days ago
by Anonymous Monk on May 21, 2008 at 20:34 UTC
    perhaps i'm missing something, but you can do it in nothing but MySQL:

    e.g. 2 days ago till today:

    SELECT [anything you want] 
FROM [anywhere you want] 
WHERE [column storing date] >= current_date()-2;

    [download]


    (from http://mysql-tips.blogspot.com/2005/04/mysql-date-calculations.html )
[reply]
[d/l]
      Actually the current_date()-#days code doesn't work when it crosses a month boundary.

      Example: 20091005 - 7 = 20090998

      Try this instead SELECT DATE_SUB(CURDATE(),INTERVAL 7 DAY)
[reply]
Re: Get the date (MySQL style) for X days ago
by ikegami (Patriarch) on May 21, 2008 at 20:43 UTC

    Your code has a bug in it if you execute it at certain times of the year. You assume that every day has 86400 seconds, but not all of them do.

    Change 

       # get the seconds since epoch
   my $epoch = timelocal($seconds, $minutes, $hours, $mday, $mon, $yea
+r);

   # calculate the number of epoch seconds since requested days ago
   my $past_epoch = $epoch - $epoch_offset;

   # get the date of X (requested) days ago
   my $past_date = localtime($past_epoch);

    [download]
    to 
       # get the seconds since epoch
   my $epoch = timegm(0,0,0, $mday, $mon, $year);

   # calculate the number of epoch seconds since requested days ago
   my $past_epoch = $epoch - $epoch_offset;

   # get the date of X (requested) days ago
   my $past_date = gmtime($past_epoch);

    [download]

    Note that even though timegm and gmtime is used, the result is using the local time zone as you intended.

    See Re^2: Get the date (MySQL style) for X days ago for an example of when your code fails.

[reply]
[d/l]
[select]

Back to Cool Uses for Perl