[Building Sakai] SAKAI_EVENT.EVENT_DATE off by one hour?
Matthew Jones
jonespm at umich.edu
Thu Sep 24 13:40:31 PDT 2009
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
>
> --
> will trillich
> "Tis the set of the sails / And not the gales / That tells the way we go."
> -- Ella Wheeler Wilcox
>
> _______________________________________________
> 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"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090924/34601246/attachment.html
More information about the sakai-dev
mailing list