all my date ties are stored with the standard :db format and being in
western Europe, I have an UTC offset +1
Time.now.at_beginning_of_day
=> 2012-02-07 00:00:00 +0100
I’ld like to know if I am right ( or wrong) in my date time based
queries like :
scope :today, lambda {
where("created_at >= ? AND created_at < ? ",
Time.now.at_beginning_of_day, Time.now.tomorrow.at_beginning_of_day)
}
which generates:
SELECT event_logs.* FROM event_logs WHERE (created_at >=
‘2012-02-06 23:00:00’ AND < ‘2012-02-07 23:00:00’ )
OR should I use the Time.now.utc to cope with the :db format ?
scope :today, lambda {
where("created_at >= ? AND created_at < ? ",
Time.now.utc.at_beginning_of_day,
Time.now.utc.tomorrow.at_beginning_of_day)
}
which generates:
SELECT event_logs.* FROM event_logs WHERE (created_at >=
‘2012-02-07 00:00:00’ AND created_at < ‘2012-02-08 00:00:00’ )
my guess is the 2nd scope , but I am not sure
thanks for your feedback
On 7 February 2012 17:12, Erwin [email protected] wrote:
where("created_at >= ? AND created_at < ? ",
scope :today, lambda {
where("created_at >= ? AND created_at < ? ",
Time.now.utc.at_beginning_of_day,
Time.now.utc.tomorrow.at_beginning_of_day)
}
which generates:
SELECT event_logs.* FROM event_logs WHERE (created_at >=
‘2012-02-07 00:00:00’ AND created_at < ‘2012-02-08 00:00:00’ )
my guess is the 2nd scope , but I am not sure
It depends which you want. Times in the db should always be in UTC,
so in the first case it is testing against the start of the day in
local time, which is 23:00 UTC. In the second case it is testing
against the start of the day UTC. So the decision is yours, if you
want your scope to return events from today local time then use the
former, if you want today UTC then use the later. It all depends on
what you mean by ‘today’.
Colin
Colin L. wrote in post #1044565:
On 7 February 2012 17:12, Erwin [email protected] wrote:
It depends which you want. Times in the db should always be in UTC,
so in the first case it is testing against the start of the day in
local time, which is 23:00 UTC. In the second case it is testing
against the start of the day UTC. So the decision is yours, if you
want your scope to return events from today local time then use the
former, if you want today UTC then use the later. It all depends on
what you mean by ‘today’.
And, by “local time” he means “server’s local time” not necessarily the
“local time” for the actual user. If you want to support the “user’s
local time” then you’ll need to know the time zone where the user
actually resides. Typically that’s just something you’ll have to ask
them when they create their account so you can store their local time
zone along with their account information.
Ughhh! +1 for Stardates.
Thanks Robert & Colin, in the current case , server and users are in
the same Time zone ( UTC+1) so server local time is user local time
( my UNIX box is UTC+1)
by today , I always mean user’s today which is local time
so as per your answer when I make a local time query, it’s normal to
have it ‘converted’ into utc for comparisons
so => all events are created at user’s local time , then stored in
UTC
if I need to query last week user’s events, then my query should use
local time ( system will convert the query times to UTC and compare
with stored db UTC times)
thanks again
On Tue, Feb 7, 2012 at 6:12 PM, Erwin [email protected] wrote:
where("created_at >= ? AND created_at < ? ",
scope :today, lambda {
thanks for your feedback
Just curious, could you not juet use Date (instead of Time).
E.g. for contract start and end dates and programming that for an
international company in a previous life, I had changed back certain
fields from the initial choice of “time” (as seemed obvious because
of the default for “created_at” etc.) to “date” (Contract#starts_date
and Contract#end_date).
So, it was then trivial to compare, understand and display the
date to the users. For the abstract concept of a “date”, I learned
the hard way to use a Date (and not Time).
So, maybe your database could have a column “creation_date”
that is really a Date and that is initialized when the record is
created in the local time zone of the user for which the Date
has a “real-life” sense. And then the start_date or creation_date
of e.g. his/her membership has an obvious local meaning.
Just my 2 cents (and venting some old frustrations I suffered over this
…)
HTH,
Peter
Hi Peter
I understand … I dream about a format Date + am:pm ! ( created
in the morning or created in the afternoon…) LOL
more, I need Time… actually I need to split events into morning /
afternoon events ( half day am:pm )
projects have half-day tasks only…
I found very useful the business_days gem, but nothing regarding
am:pm management…
On Wed, Feb 8, 2012 at 10:02 AM, Erwin [email protected] wrote:
Hi Peter
I understand … I dream about a format Date + am:pm ! ( created
in the morning or created in the afternoon…) LOL
That would be:
am = DayTimeRange.new(“00:00 until 12:00”)
pm = DayTimeRange.new(“12:00 until 24:00”)
(https://github.com/petervandenabeele/relativity)
As I write this, I see I should introduce a “00:00…12:00”
variant (with 3 dots), which includes the 00:00, but ends
just before 12:00 (actually, is 12:00:00.000000000
am or pm ? I presume it is pm).
more, I need Time… actually I need to split events into morning /
afternoon events ( half day am:pm )
projects have half-day tasks only…
I found very useful the business_days gem, but nothing regarding
am:pm management…
HTH,
Peter