Timestamps, Mapper, and MySQL
26 views
Skip to first unread message
Peter Robinett
Jul 15, 2009, 3:30:10 PM7/15/09
to Lift
As others have encountered before[1], MappedDateTime has some
limitations. Unlike others I'm not losing my time information: in my
MySQL database correct datetimes are stored. However, the datetimes
are in my local timezone, not GMT. I could change the MySQL timezone
but I'd rather not rely on that.
Given this and because I would like to store millisecond timestamps
and MySQL's native timestamp column type only stores timestamps to the
second, I figure using an unsigned BIGINT would be best. Assuming that
is a reasonable decision (please tell me if you think it isn't), what
is the best way to map to this database column?
One solution I see is to just use a MappedLong. However, then I would
have to manually convert to and from usable Dates and so on. I see
that Joda Time is a popular replacement for java.util.Date but I don't
know how to beginning using it as a MappedField. Could someone advise
me?
Finally, given the common need for timestamp fields, would it make
sense to create a MappedTimestamp MappedField? If so, how would I go
about doing this? As you can tell, I am quite new to Lift and Scala
but this would be a good way to improve my knowledge and to contribute
to Lift.
What do you think? I look forward to your thoughts!
Peter Robinett
[1]: http://groups.google.com/group/liftweb/browse_thread/thread/a3755d82f28356ea/26111ea9748c0ffc
limitations. Unlike others I'm not losing my time information: in my
MySQL database correct datetimes are stored. However, the datetimes
are in my local timezone, not GMT. I could change the MySQL timezone
but I'd rather not rely on that.
Given this and because I would like to store millisecond timestamps
and MySQL's native timestamp column type only stores timestamps to the
second, I figure using an unsigned BIGINT would be best. Assuming that
is a reasonable decision (please tell me if you think it isn't), what
is the best way to map to this database column?
One solution I see is to just use a MappedLong. However, then I would
have to manually convert to and from usable Dates and so on. I see
that Joda Time is a popular replacement for java.util.Date but I don't
know how to beginning using it as a MappedField. Could someone advise
me?
Finally, given the common need for timestamp fields, would it make
sense to create a MappedTimestamp MappedField? If so, how would I go
about doing this? As you can tell, I am quite new to Lift and Scala
but this would be a good way to improve my knowledge and to contribute
to Lift.
What do you think? I look forward to your thoughts!
Peter Robinett
[1]: http://groups.google.com/group/liftweb/browse_thread/thread/a3755d82f28356ea/26111ea9748c0ffc
g-man
Jul 18, 2009, 12:02:07 AM7/18/09
to Lift
I, too, want to get a better grip on managment of dates and times, and
for me the first step is to record everything as a UTC instance in my
DB (MySQL).
What I need to do is to have a way to make the client browser include
a 'Date' HTTP Header in every request. From that, I can get the
timezone and set a SessionVar to use in translating the particular
data back to the correct timezone in the response.
As it is now, it looks like only:
GET /ajax_request/liftAjax.js HTTP/1.1
Host:
User-Agent:
Accept:
Accept-Language:
Accept-Encoding:
Accept-Charset:
Keep-Alive:
Connection:
Referer:
Cookie:
are sent over.
Lift and Scala are fun!
> [1]:http://groups.google.com/group/liftweb/browse_thread/thread/a3755d82f...
for me the first step is to record everything as a UTC instance in my
DB (MySQL).
What I need to do is to have a way to make the client browser include
a 'Date' HTTP Header in every request. From that, I can get the
timezone and set a SessionVar to use in translating the particular
data back to the correct timezone in the response.
As it is now, it looks like only:
GET /ajax_request/liftAjax.js HTTP/1.1
Host:
User-Agent:
Accept:
Accept-Language:
Accept-Encoding:
Accept-Charset:
Keep-Alive:
Connection:
Referer:
Cookie:
are sent over.
Lift and Scala are fun!
Peter Robinett
Jul 18, 2009, 2:44:46 AM7/18/09
to Lift
Glad to hear there's interest.
However, G-Man, why do you need the browser to include a timestamp?
You can make a timestamp in your Lift code that responses to the
request. If you want to do conditional HTTP requests then you should
consider using the If-Modified-Since and If-None-Match headers.
Peter
However, G-Man, why do you need the browser to include a timestamp?
You can make a timestamp in your Lift code that responses to the
request. If you want to do conditional HTTP requests then you should
consider using the If-Modified-Since and If-None-Match headers.
Peter
g-man
Jul 22, 2009, 1:11:28 AM7/22/09
to Lift
I've been doing some research, and here's what I came up with:
1. There is one absolute time, UTC, and it has a TimeZone of 0. All
the HTTP Request/Response headers are timestamped 'GMT' which is the
same thing, basically. This is good, because we have to have one
absolute authority as to when an event occurred. The request logs I
empowered in my Jetty setup are also timestamped as UTC:
127.0.0.1 - - [22/Jul/2009:03:01:39 +0000] "GET /ajax_request/
liftAjax.js HTTP/1.1" 200
2. All records in the DB are actually saved in UTC, because there is
no TimeZone component. This is also good, because again, we need one
authority. The problem comes in when we don't actually realize these
are absolute times.
3. The Server has a TimeZone, which you can see in the Jetty console
output, not because it is explicitly noted so (although I guess I
could set that up), but because it logs as my system local time:
2009-07-21 20:51:36.062::INFO: jetty-6.1.16
You can access that through Scala via java.util.TimeZone, and with
Lift's LiftRules and TimeHelpers. This represents the amount
(milliseconds) to ADD to UTC to get LOCAL time.
4. The Client has a TimeZone, which you can access by Javascript thus:
var date = new Date();
var tzo = date.getTimezoneOffset();
The Javascript TimezoneOffset is actually useful, because it is the
amount (minutes) to ADD to LOCAL time to get UTC, although the
negative of the Java TimeZone in sign.
So, my strategy is :
1. Create a userTZO field on my User class.
2. Query the user at login via Javascript, extract the TZO, and send
it along as a hidden form parameter.
3. Upon successful login, save the TZO value to user.userTZO(). This
could even be displayed with a message such as 'Your time zone is: ...
Change it if wrong', which is also useful if JS is disabled.
4. Utilize the user's TZO to modify any date values sent over before
they are saved, because they will be in the user's local time. Thus,
all DB dates will be UTC, and the TZO will be recreated every time a
user logs in, whether from Hong Kong or Amman.
5. The Server's TZ will be used to adjust newly created objects as to
date before saving, because they will reflect the Server's local time
otherwise.
6. That way, any date math that needs to be done, such as comparing
due dates, etc, will be handled with UTC absolute times, then
converted to the user's local time for display.
In other words, if an event is due at 3:00 Los Angeles time, it will
show up as 6:00 New York time, and both will represent the same
instant in time (10:00 UTC), no matter where the user is logged in
from...
I'm about halfway to implementation!
On Jul 17, 11:44 pm, Peter Robinett <pe...@bubblefoundry.com> wrote:
> Glad to hear there's interest.
>
> However, G-Man, why do you need the browser to include a timestamp?
> You can make a timestamp in your Lift code that responses to the
> request. If you want to do conditional HTTP requests then you should
> consider using the If-Modified-Since and If-None-Match headers.
>
> Peter
>
> On Jul 17, 9:02 pm, g-man <gregor...@gmail.com> wrote:
>
> > I, too, want to get a better grip on managment of dates and times, and
> > for me the first step is to record everything as a UTC instance in my
> > DB (MySQL).
>
> > What I need to do is to have a way to make the client browser include
> > a 'Date' HTTP Header in every request. From that, I can get the
> >timezoneand set a SessionVar to use in translating the particular
> > data back to the correcttimezonein the response.
1. There is one absolute time, UTC, and it has a TimeZone of 0. All
the HTTP Request/Response headers are timestamped 'GMT' which is the
same thing, basically. This is good, because we have to have one
absolute authority as to when an event occurred. The request logs I
empowered in my Jetty setup are also timestamped as UTC:
127.0.0.1 - - [22/Jul/2009:03:01:39 +0000] "GET /ajax_request/
liftAjax.js HTTP/1.1" 200
2. All records in the DB are actually saved in UTC, because there is
no TimeZone component. This is also good, because again, we need one
authority. The problem comes in when we don't actually realize these
are absolute times.
3. The Server has a TimeZone, which you can see in the Jetty console
output, not because it is explicitly noted so (although I guess I
could set that up), but because it logs as my system local time:
2009-07-21 20:51:36.062::INFO: jetty-6.1.16
You can access that through Scala via java.util.TimeZone, and with
Lift's LiftRules and TimeHelpers. This represents the amount
(milliseconds) to ADD to UTC to get LOCAL time.
4. The Client has a TimeZone, which you can access by Javascript thus:
var date = new Date();
var tzo = date.getTimezoneOffset();
The Javascript TimezoneOffset is actually useful, because it is the
amount (minutes) to ADD to LOCAL time to get UTC, although the
negative of the Java TimeZone in sign.
So, my strategy is :
1. Create a userTZO field on my User class.
2. Query the user at login via Javascript, extract the TZO, and send
it along as a hidden form parameter.
3. Upon successful login, save the TZO value to user.userTZO(). This
could even be displayed with a message such as 'Your time zone is: ...
Change it if wrong', which is also useful if JS is disabled.
4. Utilize the user's TZO to modify any date values sent over before
they are saved, because they will be in the user's local time. Thus,
all DB dates will be UTC, and the TZO will be recreated every time a
user logs in, whether from Hong Kong or Amman.
5. The Server's TZ will be used to adjust newly created objects as to
date before saving, because they will reflect the Server's local time
otherwise.
6. That way, any date math that needs to be done, such as comparing
due dates, etc, will be handled with UTC absolute times, then
converted to the user's local time for display.
In other words, if an event is due at 3:00 Los Angeles time, it will
show up as 6:00 New York time, and both will represent the same
instant in time (10:00 UTC), no matter where the user is logged in
from...
I'm about halfway to implementation!
On Jul 17, 11:44 pm, Peter Robinett <pe...@bubblefoundry.com> wrote:
> Glad to hear there's interest.
>
> However, G-Man, why do you need the browser to include a timestamp?
> You can make a timestamp in your Lift code that responses to the
> request. If you want to do conditional HTTP requests then you should
> consider using the If-Modified-Since and If-None-Match headers.
>
> Peter
>
> On Jul 17, 9:02 pm, g-man <gregor...@gmail.com> wrote:
>
> > I, too, want to get a better grip on managment of dates and times, and
> > for me the first step is to record everything as a UTC instance in my
> > DB (MySQL).
>
> > What I need to do is to have a way to make the client browser include
> > a 'Date' HTTP Header in every request. From that, I can get the
> > data back to the correcttimezonein the response.
>
> > As it is now, it looks like only:
>
> > GET /ajax_request/liftAjax.js HTTP/1.1
> > Host:
> > User-Agent:
> > Accept:
> > Accept-Language:
> > Accept-Encoding:
> > Accept-Charset:
> > Keep-Alive:
> > Connection:
> > Referer:
> > Cookie:
>
> > are sent over.
>
> > Lift and Scala are fun!
>
> > On Jul 15, 12:30 pm, Peter Robinett <pe...@bubblefoundry.com> wrote:
>
> > > As others have encountered before[1], MappedDateTime has some
> > > limitations. Unlike others I'm not losing my time information: in my
> > > MySQL database correct datetimes are stored. However, the datetimes
> > > are in my localtimezone, not GMT. I could change the MySQLtimezone
> > As it is now, it looks like only:
>
> > GET /ajax_request/liftAjax.js HTTP/1.1
> > Host:
> > User-Agent:
> > Accept:
> > Accept-Language:
> > Accept-Encoding:
> > Accept-Charset:
> > Keep-Alive:
> > Connection:
> > Referer:
> > Cookie:
>
> > are sent over.
>
> > Lift and Scala are fun!
>
> > On Jul 15, 12:30 pm, Peter Robinett <pe...@bubblefoundry.com> wrote:
>
> > > As others have encountered before[1], MappedDateTime has some
> > > limitations. Unlike others I'm not losing my time information: in my
> > > MySQL database correct datetimes are stored. However, the datetimes
Reply all
Reply to author
Forward
0 new messages