Date Time Logic

[jarvis]

Good afternoon,

I've got myself in a muddle over what I thought should be some simple code.

Files are uploaded in a CMS and have a date field next to them (returning in format of d/m/Y). So when a new file is added, the user selects the day the file(s) should display

For example:

File A - 23/08/2022
File B - 24/08/2022
File C - 25/08/2022

Files for the previous day need to display on the current day until a set time (in this instance 13:59:59)

I've setup some code as follows:

$yesterday = new DateTime("yesterday 13:59:59", new DateTimeZone('Europe/London'));
echo "Yesterday " . $yesterday->format('d/m/Y H:i:s') . "<br/>";

date_default_timezone_set('Europe/London');
echo "Today " . date("d/m/Y h:i:s") . "<br/>";

$tomorrow = new DateTime("tomorrow 00:00:01", new DateTimeZone('Europe/London'));
echo "Tomorrow " . $tomorrow->format('d/m/Y H:i:s') . "<br/>";

So currently, this outputs:

Yesterday 23/08/2022 13:59:59
Today 24/08/2022 02:37:08
Tomorrow 25/08/2022 00:00:01

The above I use to check the date/times whilst I play around with the code logic.

I then loop through the uploaded files and dates from the CMS.

In the loop, I grab the date (stored as d/m/Y) next to each file so therefore adjust it:

$file_date = DateTime::createFromFormat('d/m/Y', $date);

Then within the loop, I also add a check to display the file if its the right day/time range:

#if today is greater than yesterday but less than tomorrow
if($file_date->format('d/m/Y H:i:s') > $yesterday->format('d/m/Y H:i:s') && $file_date->format('d/m/Y H:i:s') < $tomorrow->format('d/m/Y H:i:s') ):
  //display the file URL
endif;

It then shows File A and File B but surely should only show File A as it's after Yesterday 23/08/2022 13:59:59

I think I've missed something really obvious but can't see for looking!

Edited August 24, 2022 by jarvis
Make the file_date var clearer to prevent confusion

[Barand]

The time element of $todays_date will depend on when you run the code

$todays_date = DateTime::createFromFormat('d/m/Y', '24/08/2022');
echo $todays_date->format('Y-m-d H:i:s');                           // --> 2022-08-24 14:59:27

You only need to set the default timezone once at the top (or in your php.ini file. It will be used automatically by you new DateTime() calls after that. That's why it's called the default.

You can compare DateTime objects directly without formatting them

if ($todays_date > $yesterday)

 

[jarvis]

Thanks @Barand

I realised my naming convention wasn't the best. Therefore, I updated the original post code as $todays_date was a misleading name. This actually refers to the date associated to the file and not the actual date of today - if that makes sense?

Apologies, I caused the confusion!

I've also amended the code as per your comments.

Sadly, I still have the issue

Edited August 24, 2022 by jarvis

[Barand]

In that case, The time element of $file_date will depend on when you run the code.

Which day it appears on will depend on whether you view it in the morning or afternoon

[jarvis]

I'm using some jQuery to refresh the script every minute or so

So in theory, it should mean if someone left the page open, once it refreshes, it will remove any out of date files

[mac_gyver]

1 hour ago, jarvis said:

I then loop through the uploaded files and dates from the CMS.

if you store the dates using a DATE or DATETIME datatype, you can do this all in the query. the only looping would be to produce the output from the records that the query matches.

what you are currently doing will take longer and longer as more files get uploaded, because you are fetching all the data and looping over it to find the matching entries.

[mac_gyver]

just to summarize the operational problem - you are using code that is trying * to do datetime comparisons, with the file value only having a date part, and is ending up with the current time as the time part. * - when comparing the datetime objects directly, the d/m/Y formatted values won't be properly compared by magnitude.

if you want to just do a date comparison, you must operate on values that all only contain date parts. if you want to do a datetime comparison, you must also have the time of the uploaded file.

[Barand]

I would change your method.

A file dated 24/08/2022 is viewable from 2022-08-24 00:00:00 until  2022-08-25 13:59:59, so if the time now is between those times, show the file.

$files = [ 'FileA' => '23/08/2022',
           'FileB' => '24/08/2022',
           'FileC' => '25/08/2022'
         ];
         
foreach ($files as $fname => $date) {
    $now = new DateTime('now');
    $view = viewable($date);
    if ($view[0] <= $now && $now < $view[1] ) {
        echo $fname . '<br>';
    }
}
         
function viewable($date)
{
    $dt = DateTime::createFromFormat('d/m/Y', $date);
    $dt->setTime(0,0,0);
    $end = (clone $dt)->add(new DateInterval('P1DT14H'));
    return [$dt, $end];
}

 

[jarvis]

Many thanks for the help.

I'm trying to resolve the issue using SQL to help prevent filtering many results. Due to the way the data is stored, it's a little fiddly but will try to fathom it out.

The help on here is always very much appreciated

Thanks again

[Barand]

Applying the same logic to the SQL...

Table:file
+----+-------+------------+
| id | fname | date       |
+----+-------+------------+
| 1  | FileA | 2022-08-23 |
| 2  | FileB | 2022-08-24 |
| 3  | FileC | 2022-08-25 |
+----+-------+------------+

 

$res = $pdo->query("SELECT fname
                    FROM file
                    WHERE NOW() BETWEEN date 
                                    AND date + INTERVAL 1 DAY + interval 14 HOUR - INTERVAL 1 SECOND
                    ");
foreach ($res as $r) echo $r['fname'] . '<br>';

Output

FileB