I have an app where the user selects a date, and then my app searches for records where the created_at timestamp is the same as the date they entered… except this doesn’t exactly work, because the user is expecting MST, but created_at is stored in UTC.
Widget.where('date(created_at) = ?', input_date)
The user might enter March 12th for example. But if a record was created any time after 5PM on March 12th, then it’s going to be March 13th in the database, and the postgres date() function is going to return March 13th, when the user wants March 12th.
Looks as though a timestamp type supports an .in_time_zone call to bind them to a certain timezone. Hence I’m imagining that Widget.where(‘date(created_at) = ?’, input_date.in_time_zone(“MST”)) will sort you.
That doesn’t appear to be working completely, here’s more context if that helps:
# these are being submitted by the user, but it's just a date only.
start_date_param = params[:balance_snapshot][:start_date]
end_date_param = params[:balance_snapshot][:end_date]
start_date = Date.strptime(start_date_param, "%m/%d/%Y") if !start_date_param.blank?
end_date = Date.strptime(end_date_param, "%m/%d/%Y") if !end_date_param.blank?
if start_date && !end_date
@balance_snapshots = @balance_snapshots.where("date(created_at) = ?", start_date.in_time_zone("MST"))
end
There are records in the database where, in UTC they are March 13th. But in my time zone, they are March 12th. What I would like to do… is have postgres convert the created_at fields to MST before querying these records.
When I enter March 12 for the search date, I don’t get all of the data that I should be getting, because a bunch of the records appear as if they were entered on march 13 (in UTC that is true, but if you convert these to MST then their date is march 12th)
Here’s an example:
In the database, a record has this timestamp: Sat, 13 Mar 2021 00:54:55 UTC +00:00
If you run this timestamp through the date() function, the date returned is 2021-03-13… because it’s UTC. This timestamp is actually March 12th though if you convert it to MST! So when I search for records on the 12th… I don’t get everything from the 12th! Because the records are stored in UTC.
I don’t know how else I can explain it, lol.
When you take: Sat, 13 Mar 2021 00:54:55 UTC +00:00
and convert it to MST you get: 2021-03-12 17:54:55.914387 -0700
So you get completely different results when you run these two through the date() function.
I need postgres to treat the query as if created_at fields are actually MST.
When the user enters March 12, can’t you create timestamps for March 12 beginning and end of day in MST and search for created_at between beginning/end? The database or query layer should then convert those timestamps to UTC before querying.
I can’t believe it was that simple… I was hoping to just pass in a date, and have the same automatic conversion, but I guess it doesn’t work like that?
I now have:
start_date_param = params[:balance_snapshot][:start_date]
end_date_param = params[:balance_snapshot][:end_date]
start_date = Time.strptime(start_date_param, "%m/%d/%Y") if !start_date_param.blank?
end_date = Time.strptime(end_date_param, "%m/%d/%Y") if !end_date_param.blank?
if start_date && !end_date
@balance_snapshots = @balance_snapshots.where("created_at BETWEEN ? AND ?", start_date.beginning_of_day, start_date.end_of_day)
end
And that appears to do the job.
Is there a simpler way to write this?
Why doesn’t it do the same behavior when you do this?
Rails creates timestamps as timestamp without time zone in PostgreSQL, otherwise you could set the timezone value in your postgresql.conf and date(created_at) or created_at::date would convert the UTC timestamp to the date in your timezone as of that timestamp.
I prefer to convert any user input explicitly to UTC before continuing with any queries.
We have an app that is used all over the world and that is the only way to keep your mind sane
