当日期以不同的格式存储在数据库中时，使用Eloquent直接比较日期

Question

I am writing a program that sends emails to users when their subscription is about to expire so i have a table in my database with a date stored in varchar as 12th April 2018 I am using that format because it is easy to read therefore I don't have to convert it again later.. But when i write

$currentdate= Carbon::now()->toDateTimeString();

$threeDaystoExpire =Carbon::now()->addDays(130)->toDateTimeString();

return $expiredPacks = DB::table('subscriptions')
->whereDate('expires_on','>',$currentdate)
->whereDate('expires_on','<=',$threeDaystoExpire)
->get();

I get a blank return output but when i change expires_on to created_at that is formatted like this 2018-04-12 12:41:36 I get the correct output. I tried changing the format of $currentdate and $threeDaystoExpireto match the data in my database as advised by previous threads i read and tried again like this

$currentdate= Date('dS F Y', strtotime(Carbon::now()->toDateTimeString()));

$threeDaystoExpire =Date('dS F Y', strtotime(Carbon::now()->addDays(130)->toDateTimeString()));

return $expiredPacks = DB::table('subscriptions')
    ->whereDate('expires_on','>',$currentdate)
    ->whereDate('expires_on','<=',$threeDaystoExpire)
    ->get();

I get the same empty output. I am currently stuck. All the posts I've read only uses one value so they just make a statement to return just the date, convert it to a timestamp format then compare. I have hundreds of those date and I cant write a statement for each one so any help to tackle this problem will be greatly appreciated.

Answer 1

I made a separate field to store the date in a human readable format so I don't have to convert it later in my app, then stored the timestamp format of the same date in a separate field to handle the calculations. It is way easier to use, read and saves me the stress of converting from timestamp then later reconverting back to timestamp which doesn't make sense.

As advised here to leverage platform tools more. I decided that data that will be used in different forms will be stored in a general format and i will allow the platform handle how they want to represent those data.