CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Yaniv Ran
Yaniv Ran

Reputation: 31

Getting a different result from query with active records

I have tables with UTC dates. When trying to get the day of week by using dow in PostgreSQL, I get the wrong day if the hours flow to the next day (and I need the right dow).

I created a fiddle to show the problem:
http://sqlfiddle.com/#!15/9aa9e/11

If I run the queries locally on my pgAdmin, it will return the correct dow. But on fiddle and from rails, I get the wrong dow. Any ideas?

Upvotes: 0

Views: 68

Answers (1)

Yaniv Ran
Yaniv Ran

Reputation: 31

Ok, so I understood that pgAdmin does it's own dow magic based on my timezone, and when rails sends that, it obviously doesn't happen. The system I work on relies on the fact that rails does it's conversion magic for UTC datetimes back and forth, which work great if you don't use local postgreSQL timezone related functions such as DOW. What I did not is that it worked fine if I gave the dow the right offset. So here's my solution to the problem if anyone else needs it: 

utc_diff = Time.current.time_zone.utc_offset/60/60
# making sure the diff is either  + or - (minus is automatic) 
utc_diff = utc_diff < 0 ? utc_diff : "+#{utc_diff}"
dow_part = "EXTRACT(DOW FROM punches.punched_in_at at time zone 'UTC#{utc_diff}') as dow"

So there you have it, hope it helps others.

Upvotes: 1

Related Questions