Ruby on Rails retrieve datetime by converting it from UTC to user's time zone from database by querying date

Question

my application stores attendance datetime from user as 2014-11-23 08:42:00 by converting it to UTC, application has custom search method to search attendance only by entering date as 2014-11-23. Now problem is i have 5 attendance of date 2014-11-23 with different time and Rails stored it by converting into UTC. so for 2 record it is converted and date is changed to 2014-11-22.

my search query is 2014-11-23 and expected result is all 5 records but getting only 3. not getting other 2 because they are converted to 2014-11-22

How to get all 5 records only with entering 2014-11-23 ? Please help.

Ismael Ismael · Accepted Answer · 2014-11-23T19:32:45

If you store the datetime you can use it for that query using beginning_of_day and end_of_day methods on the time with timezone

date = Time.parse('Wed, 19 Nov 2014 19:23:59 UTC +00:00')
# => 2014-11-19 19:23:59 UTC
d = date.in_time_zone('Paris')
# => Wed, 19 Nov 2014 20:23:59 CET +01:00


User.where(created_at: (d.beginning_of_day..d.end_of_day)).count
#   (46.5ms)  SELECT COUNT(*) FROM "users"  WHERE ("users"."created_at" BETWEEN '2014-11-18 23:00:00.000000' AND '2014-11-19 22:59:59.999999')

Ruby on Rails retrieve datetime by converting it from UTC to user's time zone from database by querying date

1 Answers