ColdFusion is showing one previous date when I fetch it from the database

[yashpalm70379645]

when I am fetching the date from the database it's showing one date before.

for example - 

if date in the database is - 2020-02-26

then when i fetch to my coldfusion using select date from db it shows - 2020-02-25.

I checked for the now() which is showing correct.- showing accurate now date and time.

issue is only when i am fetching it from the database.

[yashpalm70379645]

It is happening with the azure VM only which resides on EAST US regoin. I checked the timestamp and both db server and VM belongs to the same timezone.

when I check the same on my local it is working fine but failing only on azure instance.

[WolfShade]

CFDUMP the query results, and manually compare to what you see in the database.  Something, somewhere, is altering the output.  If the dump is doing this, something is really incorrect.  If the dump is showing what's in the database, then something is altering your output.

V/r,

^ _ ^

[yashpalm70379645]

Thanks man, for the reply !

I checked it using dump also - It is showing incorrect in cfdump too...

on the above I have dump the query,

below is the actual result from database.

2019-12-29
2019-12-29
2019-12-25
2019-12-15
2019-12-12
2019-12-11
2019-12-10
2019-12-10
2019-12-09
2019-12-09
2019-12-05
2019-12-04
2019-12-03
2019-11-28
2019-03-31
2019-03-31
2019-01-31
2019-01-29
2019-01-10
2019-01-09

strange thing is same is working fine on my local CF 2018.

Issue is only on azure instances.. I checked the timestamp also which is equal for both azure instance and mysql database.

[WolfShade]

Occam's Razor.  It seems that the common denominator with the erroneous output is AWS.  Have you submitted a trouble ticket with Amazon?

I hate to say it, but I have _zero_ experience with AWS, so can't really think of anything else.  Perhaps someone else with AWS experience can chime in.  But if it works locally and not on AWS, it's most likely an AWS issue.

V/r,

^ _ ^

[BKBK]

What is the datatype of the column in which the "dates" are stored?

A test:

What happens if you assumed what you were selecting were strings, then cast to date later on? Something like this:

<cfquery name="dateTest" datasource="yourDSN">
    select snap_date
    from fcst_rv_diff_snap
</cfquery>
<cfoutput query="dateTest">
    #parsedatetime(dateTest.snap_date)# <br>
</cfoutput>

[WolfShade]

Along the lines of what BKBK suggested, you can also CAST() the value to a datetime datatype within the database query.  Depending upon what flavour of database server you're using, it should be something like:

SELECT CAST(snap_date as datetime) snap_date

FROM fcst_rv_diff_snap

If you're using Oracle, it would be:

SELECT TO_DATE(snap_date,'yyyy-mm-dd') as snap_date

FROM fcst_rv_diff_snap

V/r,

^ _ ^

[danny_gunter]

I'm having the same issue on my ColdFusion 2018 servers.  Using a MySQL 5 database, when I query via MySQL Workbench, I see the correct date (3/28/2020 for example), however, when I output it OR cfdump the query, it says the date is the previous day's date (3/27/2020 for example).

Any info as to what could be the problem is much appreciated.

[danny_gunter]

I figured out the issue.  Turns out that whenever our hosting provider does any updates or reboots the database server, the timezone resets to Greenwich Mean Time.

Running this in MySQL confirms that it's set back to "SYSTEM":

SELECT @@global.time_zone, @@session.time_zone;

Our server is in the Central Timezone, so running this SQL updates the timezone:

SET GLOBAL time_zone = 'America/Chicago';
SET SESSION time_zone = 'America/Chicago';

Hopefully this may help someone else later down the line.

Cheers!

[BKBK]

Ah, what do you know!

Thanks for sharing that, danny_gunter . Sort of makes sense, with hindsight.