Skip to main content
M
martiehen2
Inspiring
March 25, 2024
Answered

timestamp value difference with mysql

  • March 25, 2024
  • 1 reply
  • 953 views

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:

 

 

This topic has been closed for replies.
Correct answer BKBK

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?


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';

 

 

 

 

1 reply

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
March 26, 2024

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.

    M
    martiehen2Author
    Inspiring
    March 26, 2024

    @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
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 27, 2024
    quote

    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.

     

    quoteAny 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()

     

     

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices