beginning_of_month problem

Rails 2.3.2, Ruby 1.9.3

I want to get all records for one month from charges table.

recs = Charge.where(["date >= ? and date < ?", t.beginning_of_month, t.next_month.beginning_of_month])

and this transforms into this query on PostgreSQL

SELECT "charges".* FROM "charges" WHERE (date >= '2012-03-31 22:00:00.000000' and date < '2012-04-30 22:00:00.000000')

Which is wrong. Dates should have been 2012-04-01 00:00:00.000000 and 2012-05-01 00:00:00.000000

What is wrong?

by TheR

Time zones :frowning:

Peter Hickman wrote in post #1056106:

Time zones :frowning:

But how can I avoid this?

by TheR

Nothing is wrong. Those dates are the beginning of the month for the timezone that t is in, converted to UTC as times in the database are in UTC. In order to get the result you expect then make sure that t is a UTC time not a local time.

Colin

Colin Law wrote in post #1056120:

22:00:00.000000' and date < '2012-04-30 22:00:00.000000')

Which is wrong. Dates should have been 2012-04-01 00:00:00.000000 and 2012-05-01 00:00:00.000000

What is wrong?

Nothing is wrong. Those dates are the beginning of the month for the timezone that t is in, converted to UTC as times in the database are in UTC. In order to get the result you expect then make sure that t is a UTC time not a local time.

Colin

Thanks for this one. The problem lies one line before:

t = Time.new(params[:year].to_i, params[:month].to_i, 1)

And If I use:

t = Time.utc(params[:year].to_i, params[:month].to_i, 1)

it works OK.

Is this a problem of database settings or local server settings or it's the only right way to do it?

by TheR

The database is not the problem, datetimes should always be in UTC so that they are relate to particular instances in time. You could get round it by setting the server to UTC but that is also not the correct way, the app would fail if someone changed the timezone of the server which would obviously not be a good thing. The correct way is to use Time.utc as you have above.

You may also have to think about what happens when a record is written. Are you sure you are constructing the timestamp correctly, in other words is the timezone that is being used to convert the time to UTC for the database the timezone of the user? Also when the user asks for records for a specified month, which month should records with timestamps in the window where local time and UTC are in different months be shown. Life would be much simpler if the world did not go round.

Colin

Or if we decimalised time :wink: