日期插入mysql8.0向前偏移一天

dh_n

KSQL
insert into ERP_ME_WorkCalendarDetail p (p, p.version, p.fDate, p.fIsWorkingDay, p.fWorkingHours, p.fWorkCalendarMainID, p.fyear, p.fCreatePersonID, p.fCreateDateTime) values(guid(), 0, :toDate('2018-01-01'),  '是', 8.0, '7F4AC6DB9CB74FDEA9AC725EBC5FF85D', '2018',:currentPersonID() ,:currentDateTime())
显示运行日志和上面值是一样的当前日期应该是2018-08-17   10.13;不是2018-08-16 21:13:56

数据库结果
3337d4b4a1c311e8b13f00163f004758        7F4AC6DB9CB74FDEA9AC725EBC5FF85D        0        2017-12-31        是        8        2018        PSN01        2018-08-16 21:13:56                        





JDBC
insert into erp_me_workcalendardetail (fid,fDate) values ('a',STR_TO_DATE('2018-8-17','%Y-%m-%d'))


数据库结果
a                        2018-08-17                                                               




为什么会向前偏移一天？数据库是Linux系统下的mysql 版本8.0

dh_n

我查了一下说时区问题，我查数据库的时间是对的

jishuang

自己写一个java类，用标准的jdbc的方式链接数据库执行insert看看

dh_n

请看1楼，我已经用JDBC测试过了

jishuang

只有x5中操作的不正确？

把控制台打出来的完整的sql和参数值发一下看看

dh_n

1. 2018-08-17 11:23:17
   
2. exec KSQL: insert into ERP_ME_WorkCalendarDetail p (p, p.version, p.fDate, p.fIsWorkingDay, p.fWorkingHours, p.fWorkCalendarMainID, p.fyear, p.fCreatePersonID, p.fCreateDateTime) values(guid(), 0, :toDate('2018-01-01'),  '是', 8.0, 'E2DA51EB59084D38A1CEC5948BC119C3', '2018',:currentPersonID() ,:currentDateTime())
   
3. --> sql:INSERT INTO ERP_ME_WorkCalendarDetail (ERP_ME_WorkCalendarDetail.fID, ERP_ME_WorkCalendarDetail.VERSION, ERP_ME_WorkCalendarDetail.FDATE, ERP_ME_WorkCalendarDetail.FISWORKINGDAY, ERP_ME_WorkCalendarDetail.FWORKINGHOURS, ERP_ME_WorkCalendarDetail.fWorkCalendarMainID, ERP_ME_WorkCalendarDetail.FYEAR, ERP_ME_WorkCalendarDetail.FCREATEPERSONID, ERP_ME_WorkCalendarDetail.FCREATEDATETIME) VALUES (REPLACE(UUID(), '-', ''), ?,  ? , ?, ?, ?, ?,  ? ,  ? )
   
4. --> binds:[0, 2018-01-01, 是, 8.0, E2DA51EB59084D38A1CEC5948BC119C3, 2018, PSN01, 2018-08-17 11:23:17.029]
   
5. 2018-08-17 11:23:17
   
6. exec KSQL: insert into ERP_ME_WorkCalendarDetail p (p, p.version, p.fDate, p.fIsWorkingDay, p.fWorkingHours, p.fWorkCalendarMainID, p.fyear, p.fCreatePersonID, p.fCreateDateTime) values(guid(), 0, :toDate('2018-01-01'),  '是', 8.0, 'E2DA51EB59084D38A1CEC5948BC119C3', '2018',:currentPersonID() ,:currentDateTime())
   
7. --> sql:INSERT INTO ERP_ME_WorkCalendarDetail (ERP_ME_WorkCalendarDetail.fID, ERP_ME_WorkCalendarDetail.VERSION, ERP_ME_WorkCalendarDetail.FDATE, ERP_ME_WorkCalendarDetail.FISWORKINGDAY, ERP_ME_WorkCalendarDetail.FWORKINGHOURS, ERP_ME_WorkCalendarDetail.fWorkCalendarMainID, ERP_ME_WorkCalendarDetail.FYEAR, ERP_ME_WorkCalendarDetail.FCREATEPERSONID, ERP_ME_WorkCalendarDetail.FCREATEDATETIME) VALUES (REPLACE(UUID(), '-', ''), ?,  ? , ?, ?, ?, ?,  ? ,  ? )
   
8. --> binds:[0, 2018-01-01, 是, 8.0, E2DA51EB59084D38A1CEC5948BC119C3, 2018, PSN01, 2018-08-17 11:23:17.029]
   
9. 2018-08-17 11:23:17 sql 执行时间：28ms
   
10. 2018-08-17 11:23:17 sql 执行时间：28ms
    

复制代码

数据库结果  e3785201a1cc11e8b13f00163f004758        E2DA51EB59084D38A1CEC5948BC119C3        0        2017-12-31        是        8        2018        PSN01        2018-08-16 22:23:17                        
插入的日期和当前日期都变了

jishuang

jdbc和mysql中直接执行下面的都没问题？不要直接到mysql服务器上操作，在BeX5所在的服务器上操作

1. INSERT INTO ERP_ME_WorkCalendarDetail (ERP_ME_WorkCalendarDetail.fID, ERP_ME_WorkCalendarDetail.VERSION, ERP_ME_WorkCalendarDetail.FDATE, ERP_ME_WorkCalendarDetail.FISWORKINGDAY, ERP_ME_WorkCalendarDetail.FWORKINGHOURS, ERP_ME_WorkCalendarDetail.fWorkCalendarMainID, ERP_ME_WorkCalendarDetail.FYEAR, ERP_ME_WorkCalendarDetail.FCREATEPERSONID, ERP_ME_WorkCalendarDetail.FCREATEDATETIME) VALUES (REPLACE(UUID(), '-', ''), 0,  '2018-01-01' , '是', '8.0', 'E2DA51EB59084D38A1CEC5948BC119C3', '2018',  'PSN01' ,  '2018-08-17 11:23:17.029' )

复制代码

dh_n

是的，我测试过了，1楼有语句结果\
mysql
INSERT INTO ERP_ME_WorkCalendarDetail (ERP_ME_WorkCalendarDetail.fID, ERP_ME_WorkCalendarDetail.VERSION, ERP_ME_WorkCalendarDetail.FDATE, ERP_ME_WorkCalendarDetail.FISWORKINGDAY, ERP_ME_WorkCalendarDetail.FWORKINGHOURS, ERP_ME_WorkCalendarDetail.fWorkCalendarMainID, ERP_ME_WorkCalendarDetail.FYEAR, ERP_ME_WorkCalendarDetail.FCREATEPERSONID, ERP_ME_WorkCalendarDetail.FCREATEDATETIME) VALUES (REPLACE(UUID(), '-', ''), 0,  '2018-01-01' , '是', '8.0', 'E2DA51EB59084D38A1CEC5948BC119C3', '2018',  'PSN01' ,  '2018-08-17 11:23:17.029' )、


数据库结果
a08f6e1ba1cf11e8b13f00163f004758        E2DA51EB59084D38A1CEC5948BC119C3        0        2018-01-01        是        8        2018        PSN01        2018-08-17 11:23:17                       

jishuang

用我发的语句，在BeX5的服务器上用mysql客户端和java操作

不是执行insert into erp_me_workcalendardetail (fid,fDate) values ('a',STR_TO_DATE('2018-8-17','%Y-%m-%d'))

dh_n

回复快了，看8楼