[Building Sakai] SAKAI_EVENT.EVENT_DATE off by one hour?
will at serensoft.com
will at serensoft.com
Tue Sep 29 07:12:54 PDT 2009
On Fri, Sep 25, 2009 at 2:39 AM, Stephen Marquard
<stephen.marquard at uct.ac.za> wrote:
> Our datetimes in SAKAI_EVENT are in localtime (GMT+2), with mysql.
>
> Maybe something to do with server locale settings?
Arg! Let's go for a canonical answer here:
- you log in to your sakai
- quick, you check your database for now() and the most recent
"user.login" EVENT_DATE...
- they should A) match exactly since everything is UT or B) require X
hours to accomodate time-zone difference?
If B, then all system-tracking scripts (e.g. Munin) must be configured
to 'correct' for time zone? And if B, why is EVENT_DATE off but
calendar/schedule data isn't? I'm wondering if there's a time zone
filter used by some of the Sakai system and ignore by other parts...
Maybe I should have included the entire environment from the tomcat
process :) The system /etc/localtime is set for America/New York. With
the current daylight-saving time in effect, that's 4 hours off of GMT.
But the timestamps are just one hour different.
On another sample system, which is set for America/Chicago, the now()
and most recent "user.login" EVENT_DATE match 100%, which seems like a
sensible way to operate. Matthew, your remarks about oracle assuming
eastern-time by default makes me wonder if something similar going on
here... except that it's looking like central-time might be the
default instead?
>>>> "Dunstall, Christopher" <cdunstall at csu.edu.au> 9/25/2009 1:57 AM >>>
> Hi Will,
>
> As Matt said, SAKAI_EVENT records store datetimes in GMT, as are most datetimes throughout the rest of Sakai (at least in 2.4 it does - need someone to confirm if this is the case still for 2.6).
> You just need to be aware of the time difference between your timezone and GMT (sounds like 1 hour from your email) and take that into account when reviewing the records.
>
> Chris
>
>
> On 25/09/09 6:40 AM, "Matthew Jones" <jonespm at umich.edu> wrote:
>
> Event actually calls into:
> ((BaseEvent) event).m_time = timeService().newTime();
> Which returns an instance of MyTime which is the time in GMT
> ./kernel-impl/src/main/java/org/sakaiproject/time/impl/MyTime.java
>
> I actually posted up some oracle queries on a local wiki today about how the best way to query this table. Mysql probably has something similar. (convert_tz: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz)
>
> -- Oracle:
> -- For oracle if you're not in EDT you have to change to your real time zone. And will have to change to EST when you're out of daylight savings
> -- http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions092.htm
> select event_id, new_time(event_date,'GMT','EDT') as local_event_date, event_date, event, ref, context, session_id, event_code from sakai_event;
>
> To query around specific dates:
>
> -- Example: Display all of the events that occurred on 09-23-2009 between 12:00:00 and 16:00:00 EDT.
>
> select event_id, new_time(event_date,'GMT','EDT') as local_event_date, event_date, event, ref, context, session_id, event_code from sakai_event where
> event_date >= new_time(to_date('09-23-2009 12:00:00','MM-DD-YYYY HH24:MI:SS'), 'EDT', 'GMT')
> and event_date < new_time(to_date('09-24-2009 16:00:00','MM-DD-YYYY HH24:MI:SS'), 'EDT', 'GMT') order by event_date desc;
>
>
> On Thu, Sep 24, 2009 at 3:54 PM, will at serensoft.com <will at serensoft.com> wrote:
> In 2.6.x (r60737) our SAKAI_EVENT records are all off by one hour, as if there's a time zone setting we need to override (or stop overriding). Is there a setting we missed?
>
> For example, I just logged in two minutes ago. That was 3:40:47 pm, it's now 3:42:32 pm -- but the EVENT_DATE is 14:40:47 instead of 15:40:47.
>
> +---------------------+---------------------+------+------------+----------------+
> | now() | EVENT_DATE | EID | EVENT | SESSION_ACTIVE |
> +---------------------+---------------------+------+------------+----------------+
> | 2009-09-24 15:42:32 | 2009-09-24 14:40:47 | will | user.login | 1 |
> | 2009-09-24 15:42:32 | 2009-09-24 14:06:44 | will | user.login | NULL |
> | 2009-09-24 15:42:32 | 2009-09-24 13:57:12 | will | user.login | NULL |
> | 2009-09-24 15:42:32 | 2009-09-23 16:32:37 | will | user.login | NULL |
> | 2009-09-24 15:42:32 | 2009-09-23 16:04:31 | will | user.login | NULL |
> +---------------------+---------------------+------+------------+----------------+
>
> What the heck? Are there some system variables that would make Sakai stow this in a certain time zone?
>
> Calendar data, for sample contrast, seems fine. (I create an item for noon tomorrow, and it's set for noon tomorrow.)
>
> Grepping the source code for user.login didn't turn up any likely hits (lots of webservices, but that's not related to normal user logins, right?). Any pointers welcome!
>
> select
> now(),
> EVENT_DATE ,
> EID ,
> EVENT ,
> SESSION_ACTIVE
> from
> ((SAKAI_SESSION s join
> SAKAI_USER_ID_MAP m
> on((s.SESSION_USER = m.USER_ID))) join
> SAKAI_EVENT e
> on((s.SESSION_ID = e.SESSION_ID)))
> where
> EID = 'will' and
> EVENT = 'user.login'
>
> order by
> EVENT_DATE desc
> limit
> 3
>
>
> Full java command-line + environment (no time-zone specs, no user.locale, no user.region specified anywhere):
>
> /opt/jdk/bin/java
> -Djava.util.logging.config.file=/home/serensoft/deployed/sakai26x/conf/logging.properties
> -server
> -XX:+UseParallelGC
> -Xmx2000m
> -XX:MaxPermSize=500m
> -Djava.awt.headless=true
> -Dorg.apache.jasper.compiler.Parser.STRICT_QUOTE_ESCAPING=false
> -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager
> -Djava.endorsed.dirs=/home/serensoft/deployed/sakai26x/common/endorsed
> -classpath
> :/home/serensoft/deployed/sakai26x/bin/bootstrap.jar:/home/serensoft/deployed/sakai26x/bin/commons-logging-api.jar
> -Dcatalina.base=/home/serensoft/deployed/sakai26x
> -Dcatalina.home=/home/serensoft/deployed/sakai26x
> -Djava.io.tmpdir=/home/serensoft/deployed/sakai26x/temp
> org.apache.catalina.startup.Bootstrap
> start
> TERM=screen
> SHELL=/bin/bash
> SOURCE=/home/serensoft/source/sakai26x
> CATALINA_HOME=/home/serensoft/deployed/sakai26x
> SSH_CLIENT=10.3.192.64 4643 22
> SSH_TTY=/dev/pts/0
> JAVA_OPTS=-server
> -XX:+UseParallelGC
> -Xmx2000m
> -XX:MaxPermSize=500m
> -Djava.awt.headless=true
> -Dorg.apache.jasper.compiler.Parser.STRICT_QUOTE_ESCAPING=false
> -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager
> USER=serensoft
> LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:su=37;41:sg=30;43:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.svgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.aac=00;36:*.au=00;36:*.flac=00;36:*.mid=00;36:*.midi=00;
> 36:*.mka=00;36:*.mp3=00;36:*.mpc=00;36:*.ogg=00;36:*.ra=00;36:*.wav=00;36:
> TERMCAP=SC|screen|VT 100/ANSI X3.64 virtual terminal:\??:DO=\E[%dB:LE=\E[%dD:RI=\E[%dC:UP=\E[%dA:bs:bt=\E[Z:\??:cd=\E[J:ce=\E[K:cl=\E[H\E[J:cm=\E[%i%d;%dH:ct=\E[3g:\??:do=^J:nd=\E[C:pt:rc=\E8:rs=\Ec:sc=\E7:st=\EH:up=\EM:\??:le=^H:bl=^G:cr=^M:it#8:ho=\E[H:nw=\EE:ta=^I:is=\E)0:\??:li#42:co#132:am:xn:xv:LP:sr=\EM:al=\E[L:AL=\E[%dL:\??:cs=\E[%i%d;%dr:dl=\E[M:DL=\E[%dM:ks=\E[?1h\E=:\??:ke=\E[?1l\E>:vi=\E[?25l:ve=\E[34h\E[?25h:vs=\E[34l:\??:ti=\E[?1049h:te=\E[?1049l:us=\E[4m:ue=\E[24m:so=\E[3m:\??:se=\E[23m:mb=\E[5m:md=\E[1m:mr=\E[7m:me=\E[m:ms:\??:Co#8:pa#64:AF=\E[3%dm:AB=\E[4%dm:op=\E[39;49m:AX:G0:\??:as=\E(0:ae=\E(B:\??:ac=\140\140aaffggjjkkllmmnnooppqqrrssttuuvvwwxxyyzz{{||}}~~..--++,,hhII00:\??:po=\E[5i:pf=\E[4i:k0=\E[10~:k1=\EOP:k2=\EOQ:k3=\EOR:\??:k4=\EOS:k5=\E[15~:k6=\E[17~:k7=\E[18~:k8=\E[19~:\??:k9=\E[20~:k;=\E[21~:F1=\E[23~:F2=\E[24~:kb=^H:K1=\EOq:\??:K2=\EOr:K3=\EOs:K4=\EOp:K5=\EOn:kh=\E[1~:@1=\E[1~:\??:kH=\E[4~:@7=\E[4~:kN=\E[6~:kP=\E[5~:kI=\E[2~:kD=\E[3~:\??:ku=\EOA:k
> d=\EOB:kr=\EOC:kl=\EOD:l1=pf1:l2=pf2:l3=pf3:\??:l4=pf4:
> SSH_AUTH_SOCK=/tmp/ssh-ksOEGB1612/agent.1612
> MAVEN_OPTS=-Xms256m -Xmx512m -XX:PermSize=64m -XX:MaxPermSize=128m
> MAVEN_HOME=/usr
> MAIL=/var/mail/serensoft
> PATH=/opt/jdk/bin:/opt/jdk/bin:/usr/local/bin:/usr/bin:/bin:/usr/games
> STY=3776.pts-0.SakaiBB
> PWD=/home/serensoft/source/sakai_2-6-x
> JAVA_HOME=/opt/jdk
> EDITOR=vim
> LANG=en_US.UTF-8
> HISTCONTROL=ignoreboth
> SHLVL=4
> HOME=/home/serensoft
> LOGNAME=serensoft
> WINDOW=1
> SSH_CONNECTION=10.3.192.64 4643 10.3.1.11 22
> SAKAI_VERSION=sakai26x
> _=/opt/jdk/bin/java
> LD_LIBRARY_PATH=/opt/jdk1.5.0_19/jre/lib/i386/server:/opt/jdk1.5.0_19/jre/lib/i386:/opt/jdk1.5.0_19/jre/../lib/i386
>
> Chris Dunstall | Service Support - Applications
> Technology Integration/OLE Virtual Team
> Division of Information Technology | Charles Sturt University | Bathurst, NSW
>
> Ph: 02 63384818 | Fax: 02 63384181
>
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>
--
will trillich
"Tis the set of the sails / And not the gales / That tells the way we
go." -- Ella Wheeler Wilcox
More information about the sakai-dev
mailing list