CreateODBCDateTime monkeys with Daylight Saving Time!!

[Dave Morris]

If one uses CreateODBCDateTime to convert a date into the proper format for storing in a SQL Server database, and one runs this operation on a date between 0200 and 0300 on the day that Daylight Saving Time changes, the function will change the time forward or backward one hour. IT WILL DO THIS EVEN IF THE DATE YOU ARE STORING IS UTC, thus rendering UTC times INVALID because they do not have DST. Why does this function monkey with DST at all?

[Newsgroup_User]

Dave Morris wrote:
> If one uses CreateODBCDateTime to convert a date into the proper format for

it's that cf sees all datetimes as server datetimes. it doesn't know anything
about timezones, UTC, etc. see:

http://www.sustainablegis.com/blog/cfg11n/index.cfm?mode=entry&entry=77223B6A-20ED-7DEE-2AB7FBB1F37A...

[Dave Morris]

Yes, I see what you mean. In other words, because there is no such thing as 02:30 am on April 2, 2006, in my own local time (because the entire block of time from 02:00 to 02:59 becomes shifted to 03:00 to 03:59), then ColdFusion does not allow a time such as 02:30 UTC to exist either.

HEY ADOBE, THIS IS A VERY BAD SITUATION!!!! FIX IT!!!

[Newsgroup_User]

Dave Morris wrote:
> Yes, I see what you mean. In other words, because there is no such thing as
> 02:30 am on April 2, 2006, in my own local time (because the entire block of

that depends on your server's tz. as i say in that blog article the cheapest
solution is to set your server's tz to UTC. failing that, use that timezone CFC.

> HEY ADOBE, THIS IS A VERY BAD SITUATION!!!! FIX IT!!!

might not hear you, this is a better way to talking to adobe:

http://www.adobe.com/cfusion/mmform/index.cfm?name=wishform&product=12&6213=6

[Dave Morris]

What is really insidious here is that ColdFusion DOES NOT PERMIT a time of 02:00 to exist on the "spring forward" day of DST change. That means even if you are able to contort your code around, write your own version of CreateODBCDateTime so you can safely store a UTC time in a database, as soon as you try to use TimeFormat to extract it to display the time, you're screwed once again.

I have a database of over 70 MILLION record with UTC times in them from global data collection operations, and tens of thousands of them are FUCKED with no possibility of recovery. There is no way to know whether 03:30 is really supposed to be 03:30 or 02:30.

[paross1]

Why don't you use the time from the database instead of from the ColdFusion server? Or, perhaps use a date "string" in your queries and use the database cast() function it change it to a date/time object.

Phil

[Newsgroup_User]

> That means even if
> you are able to contort your code around, write your own version of
> CreateODBCDateTime so you can safely store a UTC time in a database, as soon as
> you try to use TimeFormat to extract it to display the time, you're screwed
> once again.

> I have a database of over 70 MILLION record with UTC times in them from global
> data collection operations, and tens of thousands of them are FUCKED with no
> possibility of recovery.

Surely you only use timeFormat() like this:

correct UTC data in DB -> query to CF -> timeFormat() -> UI.

So how does timeFormat() screw the data *in* your DB? It plays no part in
the STORAGE of the data, just the presentation.

--
Adam

[Dave Morris]

Do this:

Create a date object of 04/02/2006 02:30:00

Try to display it with TimeFormat()
You'll see that ColdFusion does NOT ALLOW that time to exist.

I have over 800 places where I use TimeFormat that I now have to investigate as to whether they need to be changed or not. What other methods are there of generating a formatted HH:MM display of time from a SQL Server datetime column? Then there are all of the DateAdd, DateDiff, and other time related functions that are equally unwilling to allow those times to exist, despite the fact that they are stored as UTC times.

[paross1]

For what it is worth

select cast('04/02/2006 02:30:00' as datetime) in SQL Server
and
select to_date('04/02/2006 02:30:00', 'mm/dd/yyyy hh24:mi:ss') from dual in Oracle

do not return any errors....

Phil

[Newsgroup_User]

I could replicate what the OP was saying. A nice date of 04/02/2006
02:30:00 (NB: that's MM/DD, not DD/MM) in the DB outputs as 3:30am in CF.
And this is just a plain <cfoutput>#dateColumn#</cfoutput>. No use of date
functions @ all.

Man that bites.

--
Adam

[Newsgroup_User]

Sure, I understand that, and it sux.

You'd given me the impression it somehow shagged your actual *data*.

It's easy enough to resolve, just return the time by itself from the DB.
This took me 5min (mostly reading BOL) to knock this together, and works:

select cast(datepart(hh, testDateTime) as varchar) + ':' +
cast(datepart(mi, testDateTime) as varchar) as timeOnly

It's SQL-Server-specific, but any other DB could do the same thing.

It's a bit of a hack, but it works.

--
Adam

[Dave Morris]

It HAS shagged the original data! I used CreateODBCDateTime(CreateDateTime()) to take individual year, month, day, hour, minute, second information and put it into the database. ColdFusion destroyed it by adding an hour to the times between 020000 and 025959, and now the original values are GONE!

[Newsgroup_User]

> It HAS shagged the original data! I used CreateODBCDateTime(CreateDate()) to
> take individual year, month, day, hour, minute, second information and put it
> into the database.

Right. Sorry, you'd suggested it was your use of timeFormat() that somehow
did it. Or at least that was my reading of it (the passage I quoted
earlier) anyhow.

What about the DB's log files? They will be independent of any
bullshittery CF had instigated, and should timestamp all activity, should
they not? Have you got back-ups of those?

(I'm really scratching my head here for good suggestions).

You certainly are processing a lot of data to be adding tens of thousands
of records across a one hour period, I must say. It's only that one hour
that is wrong, isn't it?

--
Adam

[Dave Morris]

I have several international web sites on this server, one of which has over 100,000 users who have contributed over 70 million records, all with UTC timestamps. The problem occurs during the one hour period EVERY YEAR when daylight saving time is started. It does not do this in the fall when the time reverts. So, yes, the problem only occurs during 1 hour out of 8,760 hours, but when you start with 70 million records over 5 or 6 years...

But the danger is for anybody who stores or manipulates a date/time OTHER THAN the date/time that is being used internally to the server. In other words, if your server is set to Central Time, and you have users in Hawaii or Arizona who need to store their own date/time, it is likely to apply Central Timezone RULES to that date/time, even if Hawaii and Arizona don't use DST.

Let's say you're scheduling operating rooms in a hospital in Honolulu. All the CF routines are going to make sure to shift those times forward an hour without warning, have a nice day. In my case, it is happening with UTC, but it could be ANY time zone that has different rules than the ones on your server.

Yes, I can probably figure out a different way to process times, but this is a massive system with thousands of different templates. In fact it affects several pretty massive software systems with millions of lines of code, so it's not as if I can just change a few SQL queries and be done with it.

It probably should be a rule for anybody operating a ColdFusion server in an international setting, where dates and times from different regions might need to be stored in a database, to set up the server on UTC time just to avoid this problem.

Me, I have to
a. find all the places this will affect in thousands of lines of code,
b. try to decide what impact going to UTC on the server will have on things like scheduled tasks, email servers, etc,
c. then figure out whether there is any way to repair tens of thousands of corrupted records

[Newsgroup_User]

Dave Morris wrote:
> I have several international web sites on this server, one of which has over
> 100,000 users who have contributed over 70 million records, all with UTC
> timestamps. The problem occurs during the one hour period EVERY YEAR when

before you blow your brains out i'd suggest doing some stats on your data to see
how many rows are potentially corrupted. is there any other data that might help
w/hints as to what the users' intention were?

> Yes, I can probably figure out a different way to process times, but this is a

i already pointed you at a few solutions but swapping your server to UTC is the
easiest.

> It probably should be a rule for anybody operating a ColdFusion server in an
> international setting, where dates and times from different regions might need
> to be stored in a database, to set up the server on UTC time just to avoid this
> problem.

unfortunately that's not always possible.

> b. try to decide what impact going to UTC on the server will have on things
> like scheduled tasks, email servers, etc,

this is by far the cheapest solution. make sure to do the same to your db and
other related servers.

[Newsgroup_User]

> but when you start with 70 million records over 5 or 6 years...

If you've not noticed any problems up until now, I'd be wondering whether
it's actually a real-world issue, rather than a theoretical one.

What are the ramifications of the dates being one hour out, on these
records?

I'm not suggesting it's a major f***-up from Adobe (well: it's
former-Macromedia's fault, but Adobe have to carry the can), but... is it
really causing you an actual problem?

--
Adam

[Newsgroup_User]

> I'm not suggesting it's a major f***-up from Adobe

I'm not suggesting it's NOT a major f***-up from Adobe

Oops.

--
Adam