16 Replies Latest reply on Feb 10, 2007 1:43 AM by Newsgroup_User

    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?
        • 1. Re: CreateODBCDateTime monkeys with Daylight Saving Time!!
          Level 7
          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-2A B7FBB1F37ABD77
          • 2. Re: CreateODBCDateTime monkeys with Daylight Saving Time!!
            Dave Morris Level 1
            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!!!

            • 3. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
              Level 7
              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
              • 4. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                Dave Morris Level 1
                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 ****** with no possibility of recovery. There is no way to know whether 03:30 is really supposed to be 03:30 or 02:30.

                • 5. CreateODBCDateTime monkeys with Daylight Saving Time!!
                  paross1 Level 2
                  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
                  • 6. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                    Level 7
                    > 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 ****** 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
                    • 7. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                      Dave Morris Level 1
                      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.


                      • 8. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                        paross1 Level 2
                        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
                        • 9. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                          Level 7
                          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
                          • 10. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                            Level 7
                            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
                            • 11. CreateODBCDateTime monkeys with Daylight Saving Time!!
                              Dave Morris Level 1
                              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!
                              • 12. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                                Level 7
                                > 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
                                • 13. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                                  Dave Morris Level 1
                                  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



                                  • 14. Re: CreateODBCDateTime monkeys with Daylight Saving     Time!!
                                    Level 7
                                    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.
                                    • 15. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                                      Level 7
                                      > 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
                                      • 16. Re: CreateODBCDateTime monkeys with Daylight Saving   Time!!
                                        Level 7
                                        > 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