Time locale / UTC question ? need someone to confirm ...

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' )

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 Law wrote in post #1044565:

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

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.....

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