MySQL datetime

[joyfedl]

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"
}

[hippo]

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;
}

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

[joyfedl]

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";
}

[hippo]

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.

[ikegami]

That factors in the time, despite instructions to the contrary

[hippo]

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;

[ysth]

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')) {

But you might have meant on or after midnight of 19 days ago.

[talexb]

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.