timestamp value difference with mysql

[martiehen2]

So, does anyone have a clue why coldfusion would return a different value from a mysql datetime?

I've got a 4 hour difference. Is there some setting I'm missing? Orm or Query. This is bizarre.

(Aside from cf adding in milliseconds of 0)

Orm record:

Database record:

Query records:

Database records:

[BKBK]

Your time-zone perhaps? In which time-zone is the ColdFusion server?

In any case, yes, strange. That's why it's often advisable to store date-times as UTC across the board. The client may be in timezone T1, the ColdFusion server in timezone T2 and the database server in timezone T3.

[martiehen2]

@BKBK I agree that most likely it is the time zone. My datasource has this in the connection string "useTimezone=true&serverTimezone=UTC"

It was my understanding that that setting would convert the datetime/timestamps to UTC for the server.
However, I was NOT expecting a conversion on reading the data back.

If that is what it is doing, then anywhere I run my code would potentially return different values from the database, depending on location/time zone. 
Any idea how to get the database server time to use in this code?

//start of code

//??? how to obtain the database server time for now()
ts=dateTimeFormat(now(),"yyyy-mm-dd HH:nn:ss");	//2024-03-25 07:44:45

//code

//end of code - see results
dbInfoQ		= new Query();
dbInfoQry	= dbInfoQ.execute(sql="SELECT * FROM MyDataTrail WHERE EntityName = 'MyEntity' AND CreatedDT > '#ts#'");
writedump(dbInfoQry);

 Thanks

[BKBK]

My datasource has this in the connection string "useTimezone=true&serverTimezone=UTC"

By martiehen2

Use just

serverTimezone=UTC

I think useTimezone is outdated, and unnecessary anyway.

Any idea how to get the database server time to use in this code?

Yes. Use either one of the MySQL functions NOW() or SYSDATE(). 

To test them, go to an online MySQL editor such as OneCompiler, and run

select now()

[BKBK]

Some code that applies the suggestions I gave above:

dbDateQuery=queryExecute("SELECT NOW() as dateNow", {}, {datasource="your_datasource_name"});

/**** Alternative ****/
// dbDateQuery=queryExecute("SELECT SYSDATE() as dateNow", {}, {datasource="your_datasource_name"});

dbDateNow=dbDateQuery["dateNow"][1];

ts=dateTimeFormat(dbDateNow,"yyyy-mm-dd HH:nn:ss");

[martiehen2]

@BKBK ok - I adjusted datasource and pulled the Now, Sysdate from database. ColdFusion is really whacked out. Any ideas?

[martiehen2]

Sorry, phpMyAdmin and dbVisualizer are 3 hours later. Time difference in coldfusion is 4 hours. Could this have something to do with Daylight Savings Time?

[BKBK · Accepted solution]

ColdFusion does not determine the timezone of third-party tools such as phpMyAdmin, dbVisualizer or MySQL. You should make sure that each individual tool is equipped with the required timezone. That is what I meant when I suggested  that you use UTC date-times "across the board." 

Some examples: 

(1)  if you want to set the timezone of the ColdFusion server to UTC, you can do so by adding the following flag to java.args in the configuration file /cfusion/bin/jvm.config.

-Duser.timezone=GMT

(2)  if you want to set the timezone of a single ColdFusion application to UTC, rather than the entire ColdFusion server, you can do so by adding the following setting to Application.cfc, 

 this.timeZone="GMT";

(3)  if you want to set the timezone of the MySQL server to UTC, you can do so by running the query

SET @@global.time_zone = '+00:00';

[martiehen2]

You should make sure that each individual tool is equipped with the required timezone. That is what I meant when I suggested  that you use UTC date-times "across the board." 

By BKBK

Well my friend, that makes absolute sense. Evidently it got missed by DevOps in the original setup. At the moment I have 8 other apps using the MySql server, whose time zone is set to "SYSTEM".  Until I've gotten together with everyone and we determine how to approach setting up UTC across the board, I changed my datasource to serverTimezone=America/New_York, which is working. Thank you so much for your patient assistance.