Beginning_of_month problem

Rails
1

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

2

Time zones :frowning:

3

On 12 April 2012 09:36, Damjan R. [email protected] wrote:

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

4

Peter H. wrote in post #1056106:

Time zones :frowning:

But how can I avoid this?

by
TheR

5

Colin L. wrote in post #1056120:

On 12 April 2012 09:36, Damjan R. [email protected] wrote:

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

6

On 12 April 2012 13:41, Colin L. [email protected] wrote:

Life would be much simpler if the world did not go round.

Or if we decimalised time :wink:

7

On 12 April 2012 11:26, Damjan R. [email protected] wrote:

timezone that t is in, converted to UTC as times in the database are

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?

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