CODEWITHSUNDEEP
ruby-on-rails
John
John

Reputation: 6612

Year query returns no results

I have a Holiday model, with a holiday_date attribute of DateTime. I added a new Holiday (New Years Day) with a date of 1/1/2019. When I do in the console Holiday.last, I see this:

#<Holiday id: 50, name: "New Years Day", holiday_date: "2018-12-31 23:00:00", created_at: "2018-11-13 13:15:54", updated_at: "2018-11-13 13:15:54">

So it is saved in UTC time, a day earlier. When I then do Holiday.last.holiday_date I get this:

Tue, 01 Jan 2019 00:00:00 CET +01:00

Great, the date is converted to our CET date and time. But when I query for a year like this:

Holiday.where("extract(year from holiday_date) = '2019'")

It returns no results. So it seems that there is no conversion to CET time with this query. How can I make sure that the query returns the holiday I added?

Upvotes: 1

Views: 45

Answers (2)

Marcin Kołodziej
Marcin Kołodziej

Reputation: 5313

You'll have to cast timezones twice:

Holiday.where(
  "extract(year from holiday_date AT TIME ZONE 'UTC' AT TIME ZONE 'CET') = '2019'"
)

This will work, but it would be nice to use indices for your query, we'll just have to prepare it better:

year = Time.zone.parse("2019-01-01")
Holiday.where("holiday_date BETWEEN ? AND ?", year.beginning_of_year, year.end_of_year)
# SELECT "holidays".* FROM "holidays" WHERE (holiday_date BETWEEN '2018-12-31 23:00:00' AND '2019-12-31 22:59:59.999999')

I would really think whether you need datetimes for your holiday_date column, perhaps dates would be enough, so that you don't have to deal with timezones.

Upvotes: 2

Vijay Atmin
Vijay Atmin

Reputation: 465

You can query by timezone like this

Holiday.where("extract(year from holiday_date AT TIME ZONE 'CET') = '2019'")

Upvotes: 1

Related Questions