0

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

Michael
  • 155
  • 15
  • What is the `date` field? Do you just want `created_at` as a `date` and not a `datetime`? – user3783243 Apr 17 '19 at 04:15
  • @user3783243 I intentionally used the `created_at` field as a datetime bcoz I think at some point I will also need to use the time. With my issue right now, I created the `date` field as a date just to compare it with the datepicker value in my web page. I have not deleted `created_at` field becoz I think I might need it for something else in the future. – Michael Apr 17 '19 at 04:24
  • no need to use date field.you can get data using created_at only – PHP Geek Apr 17 '19 at 04:26
  • @PHPGeek but what if I forgot to insert data for 4/15, and I insert it today. The created_at field will take in today's date right? When I go to web page and show data for 4/15, I will get nothing? – Michael Apr 17 '19 at 04:34
  • so if you want to check date in both column so you can use OR condition for it,it will check in both column – PHP Geek Apr 17 '19 at 04:38

2 Answers2

1

The accepted answer solves the XY problem. It's probably not the way to solve the actual problem.

There are lots of reasons for putting the current datetime into a database (rather than a datetime which is intrinsic in the data such as an appointment, or a date of birth). While this shouldn't be used for auditing purpose is it is handy for debugging and for dealing with optimistic locking.

But here, you seem to be looking for a transaction control mechanism - a way of identifying what records have been subjected to some action. If it is important to maintain a record of the sequence in which the records were processed, then a date, or even a date time, or even a millisecond timestamp may not be adequate. What happens iwhen you need to apply this operation more than once per day? What if it fails half way through and you need to resume the operation? This mechanism also precludes the notion that there may be more than 2 stati for a record.

Assuming the thing which is being done with the record is part of an ACID transaction, then there are 2 states to consider - before and after. Your data domain should explicitly describe those two states (using a null/non-null date value is merely implicit). If the transaction is not atomic then there will likely be more states to consider.

In the case of MySQL I would implement this as an enum datatype (with a non-null constraint). But more generally I seek to avoid a situation where data is being updated like this by using synchronous operations wrapped in transactions.

symcbean
  • 45,607
  • 5
  • 49
  • 83
-2

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();
Francinaldo Almeida
  • 337
  • 1
  • 10
  • @Francialdo Btw, what if I still want to retain the `datetime` format of the `created_at` field and only change the `date` part and have the `time` part auto generated in UTC format - is this possible? I am asking because right now the `time` part is set to 00:00:00 – Michael Apr 17 '19 at 09:46
  • @Francialdo I am getting `2019-04-05 00:00:00` but I need it to be something like `2019-04-05 09:25:33` where `09:25:33` is auto generated... Is this still possible with `Carbon::parse` or I need something else? – Michael Apr 17 '19 at 09:48
  • It is perfectly possible. Please refer to my updated answer. – Francinaldo Almeida Apr 17 '19 at 11:54