[Building Sakai] SAKAI_EVENT.EVENT_DATE off by one hour?

Dunstall, Christopher cdunstall at csu.edu.au
Thu Sep 24 16:57:25 PDT 2009 

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:kd=\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

More information about the sakai-dev mailing list