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

How can I convert MySQL datetime format without considering time

my $last_update = ('2025-06-30 14:40:26'); #convert to days without co
+nsidering time

if ($last_update < 20) { # if last update was done less than 20 days a
+go
print "20 days has not passed for you to update again"
}

[download]

Replies are listed 'Best First'.
Re: MySQL datetime
by hippo (Archbishop) on Jul 01, 2025 at 13:13 UTC

    Obviously, the first suggestion is to do it in the database. But if you have good reasons not to do that then consider using the core module Time::Piece:

    use strict;
use warnings;

use Time::Piece;
use Time::Seconds;
use Test::More tests => 2;

my $last_update = ('2025-06-30 14:40:26');
cmp_ok days_since ($last_update), '<', 20, "$last_update is less than 
+20 days ago";

$last_update = ('2025-05-30 14:40:26');
cmp_ok days_since ($last_update), '>', 20, "$last_update is more than 
+20 days ago";

sub days_since {
    my $stamp = shift;
    my $tp = Time::Piece->strptime (substr ($stamp, 0, 10), '%Y-%m-%d'
+);
    my $dayssince = (localtime() - $tp) / ONE_DAY;
}

    [download]

    You might want to use gmtime instead of localtime depending on how you are storing the times in your DB.

    🦛

[reply]
[d/l]

      am confused with this example, tho i want to compare from current to last update time like this way without using sub 

      use strict;
use warnings;

use Time::Piece;
use Time::Seconds qw/ ONE_DAY /;

my $last_update = ('2025-06-30 14:40:26');
my current_date = localtime;

if ($last_update < 20) {
print "20 days havent passed\n";
}
else {
  print "20 days have passed\n";
}

      [download]
[reply]
[d/l]

        Just construct $dayssince like I have done in the sub (you don't need a sub) and then compare that to the threshold of 20 in your code, rather than $last_update.

        🦛

[reply]

      That factors in the time, despite instructions to the contrary

[reply]

        Does it, though? So long as one is only concerned about whole days of difference (as appears to be the case for the OP) that fact that one of the stamps has a time doesn't matter.

        But if you want to be able to compare for fractional days then Line 17 should be amended to read

        my $dayssince = (localtime()->truncate(to => 'day') - $tp) / ONE_DAY;

        [download]

        🦛

[reply]
[d/l]
Re: MySQL datetime
by ysth (Canon) on Jul 01, 2025 at 15:30 UTC
    My first question is what timezone is the time in. If it is a TIMESTAMP field, it is in effect stored as a UTC time but whenever you select or set it it is converted to the timezone of your connection (which defaults to your server default timezone, but some client libraries silently set for you to something else). If it is a DATETIME field, it is stored in whatever timezone you provide it in (and if that is a timezone that uses daylight saving, some values will be ambiguous). For those reasons I highly suggest always using DATETIME and always storing in UTC (and avoiding all functions that use the connection timezone, like NOW(), using e.g. UTC_TIMESTAMP() instead).

    Assuming for a moment it is in your local timezone, and by "without considering time" you mean on or after midnight of 20 days ago in your local timezone, you can just do: 

    my $last_update = '2025-06-30 14:40:26';
if ($last_update ge (Time::Piece->localtime - 20*24*60*60)->truncate('
+to' => 'day')->strftime('%F')) {

    [download]
    But you might have meant on or after midnight of 19 days ago.
[reply]
[d/l]
Re: MySQL datetime
by talexb (Chancellor) on Jul 03, 2025 at 15:35 UTC

    As a general rule of thumb, if you want to do any filtering or sorting when you're dealing with a database, it's best to have the database do it.

    Alex / talexb / Toronto

    As of June 2025, Groklaw is back! This site was a really valuable resource in the now ancient fight between SCO and Linux. As it turned out, SCO was all hat and no cattle.Thanks to PJ for all her work, we owe her so much. RIP -- 2003 to 2013.

[reply]

Back to Seekers of Perl Wisdom