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

I'm using Class::DBI to interface with my MySQL db. Unfortunately, I don't get the fine-grained control of the queries I'm used to. I've had to create a trigger to update a "created_on" column with the current date/time because it's not possible to use MySQL's NOW() function.

Now I need to retrieve all rows that have been added in the past x days. I have used Class::DBI like so to create a new constructor that allows me to search based on date:

Company->add_constructor(get_recent => 'created_on > ?');

Now, I just need to figure out how to pass the new constructor a date in the correct format. I can get a date in the correct format using Time::Piece (which is what I'm using in the trigger to create the column in the first place). However, I need to take this date and subtract x days from it, so I could get all records added in the past 5 days or something. Any help would be greatly appreciated.

I searched CPAN and found tons of date modules, but I'm having a problem finding ones that implement the interval subtraction I need. Date::Calc doesn't seem to have it. I ran a supersearch as well and didn't turn up much.

Replies are listed 'Best First'.
Re: Date subtraction (intervals) w/Class::DBI
by Arunbear (Prior) on Oct 15, 2004 at 11:51 UTC
    I recommend using MySQL's built in date handling functions e.g. you could rewrite the constructor like this: 
    Company->add_constructor(
  get_recent => 'DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= created_on '
);

    [download]
    Also, what type have you used for the created_on field? If you set its type to TIMESTAMP, then MySQL will automatically update it.

    Update - This automatic update will only happen if created_on is the 1st TIMESTAMP field in the table.

[reply]
[d/l]
[select]
      Not so true anymore depending on the version you are running:

      Beginning with MySQL 4.1.2, you have more flexible control over when a
+utomatic TIMESTAMP initialization and updating occur and which column
+ should have those behaviors:


You can assign the current timestamp as the default value and the auto
+-update value, as before. But now it is possible to have just one aut
+omatic behavior or the other, or neither of them.
You can specify which TIMESTAMP column to automatically initialize or 
+update to the current date and time. This no longer need be the first
+ TIMESTAMP column.


The following discussion describes the revised syntax and behavior. No
+te that this information applies only to TIMESTAMP columns for tables
+ not created with MAXDB mode enabled. As noted earlier in this sectio
+n, MAXDB mode causes columns to be created as DATETIME columns.
</i>

      [download]
      ... more info at: MySQL doc page.


      -Waswas
[reply]
[d/l]
      That's a great suggestion, thanks a lot! I used DATETIME for created_on, and a timestamp will update it any time the record gets updated, not only when the record is created. Also, I need this for other queries, so I really appreciate your suggestion.
[reply]
Re: Date subtraction (intervals) w/Class::DBI
by xorl (Deacon) on Oct 15, 2004 at 13:47 UTC

    I agree with the person who said you should use MySQL's builtin functions. But if you can't use NOW() for some reason (I'd like to know why), it might be safe to assume you can't use the other functions.

    I use Date::Manip for a lot of stuff. While it is big it can do almost everything. I suspect it will work fine in this case.

    Another option would be to take out the day of the month and substract 5 from it. check to make sure it is still valid and the substitute that into your date. Handling the invalid range will be fun.

[reply]
      I can't generally use built-in functions with Class::DBI because when it builds queries, it puts quotes around everything, so if I try to set the field like:

      {created_on => 'NOW()'}

      It will translate that to:

      SET created_on = 'NOW()'

      Which is a string and will error out (note the quotes). It's a severe limitation of Class::DBI, but one I don't have time to fix right now.
[reply]
[d/l]
[select]
Re: Date subtraction (intervals) w/Class::DBI
by greywolf (Priest) on Oct 15, 2004 at 20:39 UTC
    I always use epoch seconds for date manipulation.

    In this instance I would convert the date to epoch seconds then subtract 5 days (in seconds of course) then convert back to your original format.


    mr greywolf
[reply]
      Thanks a bunch for your help. That's a good idea, one of those, "why didn't I think of that?"
[reply]

Back to Seekers of Perl Wisdom