bertly_the_coder wrote in post #960002:
I have been working with a couple of date operations in rails and my
answers have been pretty consistently inconsistent. I am a bit puzzled
about the difference between:
1) days_stat = user.daily_stats.where({:day => dte.midnight..
(dte.midnight + 1.day)}).first
2) days_stat = user.daily_stats.where('date(day) = ?', dte).first
I find it helpful to simplify how I think about what a Date or Time
object really is. Let's think about it as it relates to UNIX time. UNIX
time is represented as the number of seconds since January 1, 1970 UTC.
So that means UNIX time is nothing more than a simple integer value
(stored internally as a long).
Now when we ask for a range, as you show in #1 above, we are basically
creating a range of integer values. Imagine if exactly midnight today
UTC was assigned the value 0:
today_midnight = today.at_midnight
=> 0
tomorrow_midnight = today_midnight + 1.day
=> 86400
today_midnight...tomorrow_midnight
=> 0...86400
Notice I used the exclusive range operator (...) rather than the
inclusive (..). We want to include exactly midnight_today, but exclude
exactly midnight_tomorrow.
Now assuming all dates and times are stored in the database in UTC,
which I believe is the default in current versions of Rails, then
thinking in these terms works.
Now some questions to clarify your questions:
1. Are the dates your working with stored in the database as date or
datetime columns?
2. If you are looking for a specific record to be returned by .first are
you specifying an order by clause containing the relevant data/datatime
column?
In case of #1 above, if your date is stored in a date column as opposed
to datetime then a simple comparison will suffice:
today = Date.today
days_stat = user.daily_stats.where({ :day => today }).first
Looking at this from our imaginary date reference where midnight today =
0 you're basically saying this:
days_stat = user.daily_stats.where("day = 0").first
However, if :day is stored in a datetime field then it's safest, and
most efficient, assuming the :day column is indexed, to use the
exclusive range method:
today = Date.today
tomorrow = today + 1.day
days_stat = user.daily_stats.where({ :day => today...tomorrow }).first
which is like saying
days_stat = user.daily_stats.where("day >= 0 and day < 86400").first
Another possible inconsistency relates to my question #2. Assuming it is
possible to have more that one daily_stats record for a given day and
there is no ordering specified. Then calling first would be like saying,
"Give me ANY one of the records where day = today." You cannot guarantee
that records will be returned in a specific order unless you explicitly
specify that ordering in the SQL. Beyond that if the only ordering
specified was on your day column then rows with the exact date value
would have arbitrary ordering, which will likely vary depending on what
database back-end is used.
One last note about the representation of dates in other times zones
(like EST). This should not affect how you query your database, only how
the resulting date value is displayed to the end user. Store all dates
and times in UTC. Then setup your Rails application to have a default
local zone (i.e. UTC). If you want your application to be able to
display dates and time in the end user's local time zone then you need
ask them what that is when they sign up. Then store that timezone in
their user account record.
Translation of dates and times (as well as translations of numbers and
currency BTW) are part of the View layer's responsibility not the model.
However, I often see this responsibility delegated to model objects, and
in this case I think that's fine because it is convenient to do so.
Not all systems do this mixing of responsibilities though. Rudy/Rails
happens to be one that does. Other frameworks use separate formatter
classes that delegate this responsibility out to view layer objects.
Examples are (SimpleDateFormat in Java and NSDateFormatter in
Objective-C).
What's important to you is that the storage of date and time
representations at the actual model layer is consistent. If times are
stored in UTC, always store times in UTC. Don't mix representations in
the database.