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

rubyonrails-talk
1

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

2

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

3

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.

4

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

5

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

6

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

7

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