Ph: 786-539-6996

Cakephp compares timestamp with today date using Mysql

How to compare timestamp with today date? if you are working with Mysql database, the best approach is by using the mysql functions.

The advantage fg that solution is that it does not depend of the framework you are using: let say Cakephp, Laravel, etc

When we have to deal with time and date, usually is a headache.

Our task today is try to obtain from some table, for example users,  all the info where the timestamp field “created” saved in the database be equal to our date “today”.

Timestamp data is always saved with the timezone(UTC), and formatted as “YYYY-mm-dd HH:mm:ss”. It is a good method because in that case we have the same time (UTC) for all our users even though they are living in different timezone. If we have to select or show them the data, them we have to adjust the time saved to their timezone.

In order to obtain the data which was saved today, for example, we have two problems. Our data (timestamp) was saved in UTC format, but our own time is not the UTC, is our local time. Additionally, the timestamp data is saved using the formatting structure (YYYY-mm-dd HH:mm:ss). But we do not need the time part, just check if timestamp is equal to today or not;

$result = $this->ModelTable->find('all');
$result->where(['DATE(created) <'=>date('Y-m-d')]);

The best (shorter and easyly to understand) I have found is by using only Mysql functions:

First, we change the format structure in the data we are checking from our database (DATE(created).

The second part is check it with “today”, but, again, formatted in the correct structure: DATE(Y-m-d);

You can use different functions from Cakephp has, but it is a mess, at least for me.

If you want to show the data, after the previous selection, now again, because the data is in UTC , we have to change that:

$created=$user->created;
$modified=$user->modified;
$created = $created->setTimezone(‘America/New_York’);
$modified = $modified->setTimezone(‘America/New_York’);

And now just show the data to that user: <?= $created;?> or  <?= $modified;?>


 
May 4th, 2019 | aesio

Leave a Reply

Your email address will not be published. Required fields are marked *

Time Programmer Corp Since 2014 ®