Querying data stored with UTC timestamp by local timezone

Hi everyone, wondering if I might be able to get some assistance on an issue I can't seem to figure out.

Basically, I have an application whereby a "Pitch Sheet" gets created and stored in the database. I'm using the built-in created_at column. Nothing particularly fancy, except that when these records get created the created_at timestamp is in UTC, per my config/app.php file's timezone setting.

I'm pretty sure that I want to keep the timezone as UTC, as this is will be global application.

The problem is that, for example, a "pitch sheet" was created yesterday (6/13) at 10:55pm EST. However, the created_at timestamp in the database is 6/14/2018 2:55am due to the timezone being UTC.

I have a "dashboard" that displays each pitch sheet by date, i.e.:

$date = Carbon::today();
$pitchsheets = PitchSheet::whereDate('created_at', $date)->get();

So the issue we're seeing is that when trying to pull up pitch sheets for today (6/14) it's including the sheet that was actually created yesterday since the timestamp is 6/14 2:55am UTC and $date being 6/14 EST.

So, one approach to fixing this might be to add another date field for the "applicable date", which would store "today's" date, then I could query based on that. But what I'm trying to figure out is whether or not there's a way to convert the created_at timestamp from the database to EST when using whereDate() and compare it to $date?

(eventually the timezone will need to be different by user, but for now I'm just wanting to hardcode it to EST)

I'm assuming I could use whereRaw and use some sort of casting/date conversion in the SQL query itself, but I'm hoping for a more native way to Laravel to accomplish this.

Any thoughts?

Thanks

Cronix

7 years ago

Best Answer

Level 67

You don't need another date field for this. You just need to convert the date you're searching on from your local timezone to UTC before performing the query.

// get today's date for the America/Los_Angeles tz and convert it to UTC
$date = Carbon::today('America/Los_Angeles')->timezone('UTC');

keithbrinks

7 years ago

Level 5

Hi Cronix,

Thanks for the response. Not sure that solves the problem though.

The result of $date = Carbon::today(); is 2018-06-14 00:00:00.0 UTC

The result of Carbon::today('America/Detroit')->timezone('UTC') is

2018-06-14 04:00:00.0 UTC

In either case, when the query runs:

whereDate('created_at', $date)

It still fetches the record where created_at = "2018-06-14 02:55:40.580", even though the EST timestamp should actually be "2018-06-13 22:55:40.580".

Does that make sense? Not sure the best way to explain it.

Cronix

7 years ago

Level 67

Oh, I think it's the opposite then. Create the date in UTC and convert it to your local tz

$date = Carbon::today()->timezone('America/Los_Angeles');

keithbrinks

7 years ago

Level 5

Ok, so, that still wasn't quite giving me the results I was looking for due to the date being stored as the "next day".

I took your first suggestion of first taking Carbon::today() and converting to the local timezone Carbon::today('America/Detroit'). This gives me a timestamp for the start of today (Eastern time).

I then created a second Carbon instance to give me the end of day timestamp Carbon::today('America/Detroit')->endOfDay().

I then changed my whereDate to a where, where I'm then converting the timestamp to UTC and grabbing records where created_at are between the day's start date and end of day timestamps:

whereBetween('created_at', [$start_of_day->timezone('UTC')->toDateTimeString(), $end_of_day->timezone('UTC')->toDateTimeString()])

This seems to be giving me my expected results.

These timestamps are tricky! Thanks for the help. :)

4 likes

Please or to participate in this conversation.