TimeZone问题与ibatis ORM和postgres(TimeZone issue with ibatis ORM and postgres)

TimeZone问题与ibatis ORM和postgres(TimeZone issue with ibatis ORM and postgres)

请建议，我正在使用postgres数据库中的ibatis ORM检索数据。

当我使用ibatis触发下面的查询时，时间戳“PVS.SCHED_DTE”减少到“2013-06-07 23：30：00.0”，其中db时间戳是“2013-06-08 05：00：00.0”。

虽然使用postgresAdminII执行相同的查询时，它会给出正确的PVS.SCHED_DTE。

SELECT PVS.PTNT_VISIT_SCHED_NUM, PVS.PTNT_ID,P.FIRST_NAM AS PTNT_FIRST_NAM,P.PRIM_EMAIL_TXT,P.SCNDRY_EMAIL_TXT, PVS.PTNT_NOTIFIED_FLG,PVS.SCHED_DTE,PVS.VISIT_TYPE_NUM,PVS.DURTN_TYPE_NUM,PVS.ROUTE_TYPE_NUM, C.FIRST_NAM AS CARE_FIRST_NAM,C.MIDDLE_INIT_NAM AS CARE_MIDDLE_NAM,C.LAST_NAM AS CARE_LAST_NAM, C.PHONE_NUM_1,ORGNIZATN.EMAIL_HOST_SERVER_TXT,ORGNIZATN.PORT_NUM, ORGNIZATN.MAIL_SENDER_USER_ID,ORGNIZATN.MAIL_SENDER_PSWD_DESC,ORGNIZATN.SOCKET_FCTRY_PORT_DESC, ORGNIZATN.TIMEZONE_ID FROM IHCAS.PTNT_VISIT_SCHED PVS , IHCAS.PTNT P, IHCAS.ROUTE_LEG RL, IHCAS.ROUTE R, IHCAS.CAREGIVER C,IHCAS.ORG ORGNIZATN WHERE ORGNIZATN.ORG_NUM = ? AND PVS.SCHED_STAT_CDE = '2002' AND PVS.PTNT_NOTIFIED_FLG = FALSE AND **PVS.SCHED_DTE >= (CURRENT_DATE + interval '1 day') AT TIME ZONE ? at TIME ZONE 'UTC'** AND **PVS.SCHED_DTE < (CURRENT_DATE + interval '2 day' - interval '1 sec') AT TIME ZONE ? at TIME ZONE 'UTC'** AND PVS.PTNT_ID = P.PTNT_ID AND PVS.PTNT_VISIT_SCHED_NUM = RL.PTNT_VISIT_SCHED_NUM AND RL.ROUTE_NUM = R.ROUTE_NUM AND C.CAREGIVER_NUM=R.ASGN_TO_CAREGIVER_NUM AND PVS.ORG_NUM= ORGNIZATN.ORG_NUM ORDER BY PVS.SCHED_DTE ASC,PVS.ROUTE_TYPE_NUM ASC,PVS.ORG_NUM ASC Parameters: **[1, EST, EST]** Header: [PTNT_VISIT_SCHED_NUM, PTNT_NOTIFIED_FLG, **SCHED_DTE**, VISIT_TYPE_NUM, DURTN_TYPE_NUM, ROUTE_TYPE_NUM, PTNT_ID, PTNT_FIRST_NAM, PRIM_EMAIL_TXT, SCNDRY_EMAIL_TXT, CARE_FIRST_NAM, CARE_MIDDLE_NAM, CARE_LAST_NAM, PHONE_NUM_1, EMAIL_HOST_SERVER_TXT, PORT_NUM, MAIL_SENDER_USER_ID, MAIL_SENDER_PSWD_DESC, SOCKET_FCTRY_PORT_DESC, TIMEZONE_ID] Result: [1129, false, **2013-06-07 23:30:00.0**, 1002, 1551, 1702, PID146, Ricky, Receiver.test@******.com, , Vikas, S, Jain, 956-**-5**8, 172.17.*.***, 25, Sender.test@******.com, ********123, 465, EST]

对我来说..似乎是一些时区问题，但是什么以及如何解决。 注意：本地时区：IST应用时区：EST在数据库中，列的数据类型是没有时区的时间戳。

有什么建议.. ??

Please suggest, I am retrieving data using ibatis ORM from postgres database.

When I fire the below query using ibatis the timestamp "PVS.SCHED_DTE" get reduced to "2013-06-07 23:30:00.0" where as in db timestamp is "2013-06-08 05:00:00.0".

Though when the same query is executed using postgresAdminII, its gives proper PVS.SCHED_DTE.

SELECT PVS.PTNT_VISIT_SCHED_NUM, PVS.PTNT_ID,P.FIRST_NAM AS PTNT_FIRST_NAM,P.PRIM_EMAIL_TXT,P.SCNDRY_EMAIL_TXT, PVS.PTNT_NOTIFIED_FLG,PVS.SCHED_DTE,PVS.VISIT_TYPE_NUM,PVS.DURTN_TYPE_NUM,PVS.ROUTE_TYPE_NUM, C.FIRST_NAM AS CARE_FIRST_NAM,C.MIDDLE_INIT_NAM AS CARE_MIDDLE_NAM,C.LAST_NAM AS CARE_LAST_NAM, C.PHONE_NUM_1,ORGNIZATN.EMAIL_HOST_SERVER_TXT,ORGNIZATN.PORT_NUM, ORGNIZATN.MAIL_SENDER_USER_ID,ORGNIZATN.MAIL_SENDER_PSWD_DESC,ORGNIZATN.SOCKET_FCTRY_PORT_DESC, ORGNIZATN.TIMEZONE_ID FROM IHCAS.PTNT_VISIT_SCHED PVS , IHCAS.PTNT P, IHCAS.ROUTE_LEG RL, IHCAS.ROUTE R, IHCAS.CAREGIVER C,IHCAS.ORG ORGNIZATN WHERE ORGNIZATN.ORG_NUM = ? AND PVS.SCHED_STAT_CDE = '2002' AND PVS.PTNT_NOTIFIED_FLG = FALSE AND **PVS.SCHED_DTE >= (CURRENT_DATE + interval '1 day') AT TIME ZONE ? at TIME ZONE 'UTC'** AND **PVS.SCHED_DTE < (CURRENT_DATE + interval '2 day' - interval '1 sec') AT TIME ZONE ? at TIME ZONE 'UTC'** AND PVS.PTNT_ID = P.PTNT_ID AND PVS.PTNT_VISIT_SCHED_NUM = RL.PTNT_VISIT_SCHED_NUM AND RL.ROUTE_NUM = R.ROUTE_NUM AND C.CAREGIVER_NUM=R.ASGN_TO_CAREGIVER_NUM AND PVS.ORG_NUM= ORGNIZATN.ORG_NUM ORDER BY PVS.SCHED_DTE ASC,PVS.ROUTE_TYPE_NUM ASC,PVS.ORG_NUM ASC Parameters: **[1, EST, EST]** Header: [PTNT_VISIT_SCHED_NUM, PTNT_NOTIFIED_FLG, **SCHED_DTE**, VISIT_TYPE_NUM, DURTN_TYPE_NUM, ROUTE_TYPE_NUM, PTNT_ID, PTNT_FIRST_NAM, PRIM_EMAIL_TXT, SCNDRY_EMAIL_TXT, CARE_FIRST_NAM, CARE_MIDDLE_NAM, CARE_LAST_NAM, PHONE_NUM_1, EMAIL_HOST_SERVER_TXT, PORT_NUM, MAIL_SENDER_USER_ID, MAIL_SENDER_PSWD_DESC, SOCKET_FCTRY_PORT_DESC, TIMEZONE_ID] Result: [1129, false, **2013-06-07 23:30:00.0**, 1002, 1551, 1702, PID146, Ricky, Receiver.test@******.com, , Vikas, S, Jain, 956-**-5**8, 172.17.*.***, 25, Sender.test@******.com, ********123, 465, EST]

For me.. it seems to be some timezone issue but what and how to resolve. N.B : Local Time zone : IST Application Time zone : EST In database, datatype of column is timestamp without timezone.

Any suggestion.. ??

最满意答案

最好将数据库时区设置为UTC并实现UTC保留的MyBatis类型处理程序，如接受的答案所示： 使用Java，iBatis和Oracle处理UTC日期时间的正确方法是什么？ 。

It may be best to set the database timezone to UTC and implement an UTC-preserving MyBatis type handler as shown in the accepted answer to this: What's the right way to handle UTC date-times using Java, iBatis, and Oracle?.