Search code examples
phpmysqllaraveldatabase-management

MySQL: Is it good to define a new date column to determine what date a record has been fetched when there is already a created_at column?

I need some help about database management. I am trying to retrieve data from my database (filtering them by the created_at field).

There will be no problem when I am retrieving data from my database created in today's date.

For example today is 4/17. When I run the insert function today, the value for created_at will be 4/17 as well. So when I go to my web page and display data for 4/17, the data will be right.

But let's say I forgot to fetch data for 4/15, and I need to fetch those data today. When I insert these data in my database now, the created_at will be 4/17, but the adjacent data is actually for 4/15.

Now, when I go to my web page and display data for 4/15, I will get nothing.

As a workaround, I added a date field in my table, and this will contain a specified date, unlike the created_field that takes the server's date. I now use the date field to filter the data in my web page.

However, I think this is somewhat redundant or inefficient approach. Does anyone have any suggestions?

Here is a screenshot of my current table structure: enter image description here

Solution

  • Since you are using Laravel, you can simply override the created_at value when creating your model. So for example, you can do the following:

    $myModel->created_at = Carbon::parse('2019-04-15');
$myModel->save();

    This would set the created_at value to April 15th, not today. Hence you don't need a second date column in your table.

    UPDATE

    Nonetheless, if you need the time part to still reflect the current time, you can do the following:

    $myModel->created_at = Carbon::now()->setYear(2019)->setMonth(4)->setDay(15);
$myModel->save();