http://coldfusion.com logo
Join Slack
Powered by
Having an issue where time is not correct when ins...
# lucee
r
Having an issue where time is not correct when inserting into mysql. My Lucee server is set to EST & showing correct time. MySQL database is showing correct EST when select now(); I've tried with both timestamp and datetime fields and the time is off 4 hours when the insert happens using createdAt = now(); Anyone have a clue what I could be missing?
m
The datasource also has a timezone setting. I think it defaults to use the database timezone though, so that may not be the issue.
r
@Matt Dyer Thanks I'll take a look
By default it was set to same as lucee instance. I changed it to EST and made no difference. Thanks for the idea though.
message has been deleted
j
was 
this.timezone
defined in your application.cfc?
r
Interesting, everything is set to EST but it seems like the time it's inserting is UTC
@jumpmaster Good idea. I'm not setting it in Application.cfc though
oh well, now that I know it's UTC it's inserting I guess I will just convert it for display.
j
Does GETDATE() return a different result?
r
@jumpmaster Is that SQL server? I'm using mysql and I don't think it's available. It errors for me.
Copy code
select current_timestamp();
select now();
I did use this on mysql and it returns correct time
j
Yes it is, misread your description
r
Maybe Lucee latest does use UTC for inserting time? If so, I wonder if you can change that behavior?
j
You can always pass a time zone argument into dateFormat, but there should be a better way to set your default time zone to EST
r
What's weird is if I dump writedump(getTimezoneInfo()); on the page that is doing the insert it is all correct EST like image above. DB connection is also set to EST. The JVM is also showing America/New York.
I'm going to assume it's the mySQL 8 server at this point, If I do
Copy code
select timediff( now(), utc_timestamp() );
-04:00:00
so now () gives me correct time but maybe by default it stores UTC. It's fine as long as I know this. I can display with UTC2Local. When I check server global time it says SYSTEM and system is EST.
g
I wouldn't assume mysql is "just" storing in UTC and I wouldn't "assume" the driver is to blame. It may certainly be the case - but if you just assume it - then put in code to workaround it - and it turns out to be something else - you'll have a database filled with "mixed" timezones and that will be a pain in the ass! If it is "just" storing in UTC - despite the server timezone and the DB timezone. Then I would be suggesting a bug in the mysql version or the driver. Also, if you can change to storing date/times in UTC - then I would certainly recommend that path. That way it is ALWAYS UTC on disk and you only ever have to worry about getting the timezone from the browser and doing a LS / dateformat for display. (or storing a users timezone in their profile and using that. Even if you default everyone to EST - have it configurable - for the future.) (if you already have dates stored / make sure they're all updated, specifically / deliberately to UTC) Also just while thinking about dates.... if mysql is on a windows server, Get the timezone files from: https://dev.mysql.com/downloads/timezones.html Extract and copy the files into the "data/mysql" directory of the install. Restart the DB service.
r
@gavinbaumanis Great advice. I'll post back if I can find out what's going on. My db driver is a few minor versions behind. I'll try that first. In the past when I queried the server now() time it would be off and then I could make changes. This time the db server is showing the correct time when queried so I haven't experienced this before.
👍🏼 1
g
Let us know how get along and what you finally decide.
@risto just another idea... did you restart the Lucee server after changing the timezone in the datasource? I don't know that will fix the anything - just another "square" in the checklist of possibilities.
Open in Slack
PreviousNext
Powered by