Re: [htoolkit-users] time zone bug

[<bri...@cs...>]

I found this very old mail in my inbox, sorry for not responding sooner. 
Yeah, a new HSQL release would be a good idea, IMO. Krasimir is the 
maintainer, so it's really up to him.

/Björn

Frederik Eaton wrote:
> Hi Bjorn,
> 
> The sourceforge page for HSQL shows a most recent release date of
> December 15, 2005 - over a year ago, presumably not including the
> timezone patch which I think fixes a rather serious bug, which caused
> a lot of problems for me when I tried to use HSQL for my master's
> project. Perhaps it might be helpful to other users if the fix were
> made available to them as well?
> 
> Thanks,
> 
> Frederik
> 
> On Fri, Aug 25, 2006 at 11:20:55AM +0200, Björn Bringert wrote:
>> Sorry for the delay, I've been on vacation. The timezone patch has been 
>> committed. I haven't added the Makefile, since I think that some cabal 
>> tools that will come with GHC 6.6 could make it obsolete.
>>
>> /Björn
>>
>> Frederik Eaton wrote:
>>> Hi,
>>>
>>> Oops, sorry, there was a bug. Here's a more-tested patch. Stay tuned!
>>>
>>> (if you've already applied the last one, change HSQL.hsc's
>>>
>>>       ctMonth = toEnum $ mon,
>>>
>>> to
>>>
>>>       ctMonth = toEnum $ (mon-1),
>>>
>>> )
>>>
>>> Frederik
>>>
>>> On Thu, Aug 17, 2006 at 01:49:40AM +0100, Frederik Eaton wrote:
>>>
>>>> I'm attaching a patch which I've tested.
>>>>
>>>> I'm also attaching a Makefile derived from Bulat's IIRC, it might be
>>>> good to include it in those directories which contain a .cabal file.
>>>>
>>>> Thanks,
>>>>
>>>> Frederik
>>>>
>>>> On Thu, Aug 17, 2006 at 01:17:11AM +0100, Frederik Eaton wrote:
>>>>
>>>>> Hi, in
>>>>>
>>>>> HSQL/Database/HSQL.hsc
>>>>>
>>>>> instance SqlBind ClockTime where
>>>>> ...
>>>>>
>>>>> all of the 'fromSqlValue' functions convert with respect to the local
>>>>> timezone, while 'toSqlValue' converts with respect to UTC. Thus the
>>>>> values being written are not the same, it would seem, as those being
>>>>> read. Certainly that is the case for me.
>>>>>
>>>>> If the three appearances of currTZ within the body of that instance
>>>>> (and not elsewhere) are changed to 0, it should fix the problem (but I
>>>>> haven't tested).
>>>>>
>>>>> Also, I don't understand why a custom function 'mkClockTime' has been
>>>>> written in that module (it calls 'mktime' via FFI), rather than
>>>>> calling the standard library function toClockTime which seems to do
>>>>> the same thing.
>>>>>
>>>>> Frederik
>>>>>
>>>>> On Thu, Aug 10, 2006 at 04:19:36PM +0100, Frederik Eaton wrote:
>>>>>
>>>>>> In HSQL, why are 'datetime' columns interpreted as being in the local
>>>>>> timezone?
>>>>>>
>>>>>> I would think that UTC would be more useful. If the local timezone is
>>>>>> used, then it makes it more difficult to move a server between
>>>>>> timezones, or to have multiple servers in multiple timezones.
>>>>>>
>>>>>> Frederik
>>>>>>
>>>>>> On Sat, Aug 05, 2006 at 06:59:21PM +0100, Frederik Eaton wrote:
>>>>>>
>>>>>>> Thanks!
>>>>>>>
>>>>>>> On Sat, Aug 05, 2006 at 05:27:04PM +0200, Bjorn Bringert wrote:
>>>>>>>
>>>>>>>> On Aug 5, 2006, at 3:34 PM, Frederik Eaton wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>> On Sat, Aug 05, 2006 at 11:15:19AM +0200, Bjorn Bringert wrote:
>>>>>>>>>
>>>>>>>>>> On Jul 31, 2006, at 12:55 PM, Frederik Eaton wrote:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>> Hi,
>>>>>>>>>>>
>>>>>>>>>>> I'm attaching another HSQL patch.
>>>>>>>>>>>
>>>>>>>>>>> It allows users to specify a port number with the server name in the
>>>>>>>>>>> MySQL client.
>>>>>>>>>>>
>>>>>>>>>>> Frederik
>>>>>>>>>> Hi Frederik,
>>>>>>>>>>
>>>>>>>>>> the patch doesn't apply cleanly against the current CVS version. Is
>>>>>>>>>> there any chance you could make a patch against the CVS version
>>>>>>>>>> instead?
>>>>>>>>> It's a very simple modification, can't you just copy and paste from
>>>>>>>>> the patch?
>>>>>>>> It appears to have been a simple whitespace mismatch problem (tabs in the original HSQL source). I 
>>>>>>>> have committed your patch to CVS.
>>>>>>>>
>>>>>>>> Thanks!
>>>>>>>>
>>>>>>>> /Björn
>>>> -- 
>>>> http://ofb.net/~frederik/
>>>
>>>> diff -ur HSQL-1.7-modified/HSQL/Database/HSQL.hsc HSQL-1.7-modified-2/HSQL/Database/HSQL.hsc
>>>> --- HSQL-1.7-modified/HSQL/Database/HSQL.hsc	2005-12-15 20:57:18.000000000 +0000
>>>> +++ HSQL-1.7-modified-2/HSQL/Database/HSQL.hsc	2006-08-17 01:43:19.000000000 +0100
>>>> @@ -304,28 +304,17 @@
>>>>
>>>> 	toSqlValue d = show d
>>>>
>>>> -mkClockTime :: Int -> Int -> Int -> Int -> Int -> Int -> Int -> ClockTime
>>>> mkClockTime year mon mday hour min sec tz =
>>>> -	unsafePerformIO $ do
>>>> -		allocaBytes (#const sizeof(struct tm)) $ \ p_tm -> do
>>>> -			(#poke struct tm,tm_sec  ) p_tm	(fromIntegral sec  :: CInt)
>>>> -			(#poke struct tm,tm_min  ) p_tm	(fromIntegral min  :: CInt)
>>>> -			(#poke struct tm,tm_hour ) p_tm	(fromIntegral hour :: CInt)
>>>> -			(#poke struct tm,tm_mday ) p_tm	(fromIntegral mday :: CInt)
>>>> -			(#poke struct tm,tm_mon  ) p_tm	(fromIntegral (mon-1) :: CInt)
>>>> -			(#poke struct tm,tm_year ) p_tm	(fromIntegral (year-1900) :: CInt)
>>>> -			(#poke struct tm,tm_isdst) p_tm	(-1 :: CInt)
>>>> -			t <- mktime p_tm
>>>> -#if __GLASGOW_HASKELL__ >= 603
>>>> -			return (TOD (fromIntegral (fromEnum t) + fromIntegral (tz-currTZ)) 0)
>>>> -#else
>>>> -			return (TOD (fromIntegral t + fromIntegral (tz-currTZ)) 0)
>>>> -#endif
>>>> -foreign import ccall unsafe mktime :: Ptr () -> IO CTime
>>>> -
>>>> -{-# NOINLINE currTZ #-}
>>>> -currTZ :: Int
>>>> -currTZ = ctTZ (unsafePerformIO (getClockTime >>= toCalendarTime))                  -- Hack
>>>> +  toClockTime $ CalendarTime {
>>>> +      ctYear = year,
>>>> +      ctMonth = toEnum $ mon,
>>>> +      ctDay = mday,
>>>> +      ctHour = hour,
>>>> +      ctMin = min,
>>>> +      ctSec = sec,
>>>> +      ctPicosec = 0,
>>>> +      ctTZ = tz
>>>> +    }
>>>>
>>>> parseTZ :: ReadP Int
>>>> parseTZ =  (char '+' >> readDecP) `mplus` (char '-' >> fmap negate readDecP)
>>>> @@ -373,14 +362,14 @@
>>>> 			getTime :: ReadP ClockTime
>>>> 			getTime = do
>>>> 				  (hour, minutes, seconds) <- readHMS
>>>> -				  return (mkClockTime 1970 1 1 hour minutes seconds currTZ)
>>>> +				  return (mkClockTime 1970 1 1 hour minutes seconds 0)
>>>>
>>>> 	fromSqlValue SqlDate s = f_read getDate s
>>>> 		where
>>>> 			getDate :: ReadP ClockTime
>>>> 			getDate = do
>>>> 				  (year, month, day) <- readYMD
>>>> -				  return (mkClockTime year month day 0 0 0 currTZ)
>>>> +				  return (mkClockTime year month day 0 0 0 0)
>>>>
>>>> 	fromSqlValue SqlDateTimeTZ s = f_read getDateTimeTZ s
>>>> 		where
>>>> @@ -400,7 +389,7 @@
>>>> 			getDateTime :: ReadP ClockTime
>>>> 			getDateTime = do
>>>> 				(year, month, day, hour, minutes, seconds) <- readDateTime
>>>> -				return (mkClockTime year month day hour minutes seconds currTZ)
>>>> +				return (mkClockTime year month day hour minutes seconds 0)
>>>>
>>>> 	fromSqlValue _ _ = Nothing
>>>>
>>>
>>>> # This Makefile is mostly a wrapper around Setup.hs for people who
>>>> # just want to type make.
>>>>
>>>> CAS=runhaskell Setup.lhs
>>>> CONFOPTS ?= --user --prefix=${HOME}
>>>> INSTOPTS ?= --user
>>>>
>>>> all: build
>>>>
>>>> configure: .setup-config
>>>> .setup-config: HSQL.cabal
>>>> 	${CAS} configure ${CONFOPTS}
>>>>
>>>> build: configure
>>>> 	${CAS} build
>>>>
>>>> install: build
>>>> 	${CAS} install ${INSTOPTS}
>>>>
>>>> uninstall: unregister
>>>> unregister: configure
>>>> 	${CAS} unregister
>>>>
>>>> clean:
>>>> 	-runhaskell Setup.hs clean
>>>> 	-rm -rf dist
>>>> 	-rm -f .setup-config
>>>>
>>>> .PHONY: all configure build install uninstall unregister clean
>>>
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>> diff -ur HSQL-1.7-modified/HSQL/Database/HSQL.hsc HSQL-1.7-modified-2/HSQL/Database/HSQL.hsc
>>> --- HSQL-1.7-modified/HSQL/Database/HSQL.hsc	2005-12-15 20:57:18.000000000 +0000
>>> +++ HSQL-1.7-modified-2/HSQL/Database/HSQL.hsc	2006-08-17 22:04:23.000000000 +0100
>>> @@ -304,28 +304,17 @@
>>>  
>>>  	toSqlValue d = show d
>>>  
>>> -mkClockTime :: Int -> Int -> Int -> Int -> Int -> Int -> Int -> ClockTime
>>>  mkClockTime year mon mday hour min sec tz =
>>> -	unsafePerformIO $ do
>>> -		allocaBytes (#const sizeof(struct tm)) $ \ p_tm -> do
>>> -			(#poke struct tm,tm_sec  ) p_tm	(fromIntegral sec  :: CInt)
>>> -			(#poke struct tm,tm_min  ) p_tm	(fromIntegral min  :: CInt)
>>> -			(#poke struct tm,tm_hour ) p_tm	(fromIntegral hour :: CInt)
>>> -			(#poke struct tm,tm_mday ) p_tm	(fromIntegral mday :: CInt)
>>> -			(#poke struct tm,tm_mon  ) p_tm	(fromIntegral (mon-1) :: CInt)
>>> -			(#poke struct tm,tm_year ) p_tm	(fromIntegral (year-1900) :: CInt)
>>> -			(#poke struct tm,tm_isdst) p_tm	(-1 :: CInt)
>>> -			t <- mktime p_tm
>>> -#if __GLASGOW_HASKELL__ >= 603
>>> -			return (TOD (fromIntegral (fromEnum t) + fromIntegral (tz-currTZ)) 0)
>>> -#else
>>> -			return (TOD (fromIntegral t + fromIntegral (tz-currTZ)) 0)
>>> -#endif
>>> -foreign import ccall unsafe mktime :: Ptr () -> IO CTime
>>> -
>>> -{-# NOINLINE currTZ #-}
>>> -currTZ :: Int
>>> -currTZ = ctTZ (unsafePerformIO (getClockTime >>= toCalendarTime))                  -- Hack
>>> +  toClockTime $ CalendarTime {
>>> +      ctYear = year,
>>> +      ctMonth = toEnum $ (mon-1),
>>> +      ctDay = mday,
>>> +      ctHour = hour,
>>> +      ctMin = min,
>>> +      ctSec = sec,
>>> +      ctPicosec = 0,
>>> +      ctTZ = tz
>>> +    }
>>>  
>>>  parseTZ :: ReadP Int
>>>  parseTZ =  (char '+' >> readDecP) `mplus` (char '-' >> fmap negate readDecP)
>>> @@ -373,14 +362,14 @@
>>>  			getTime :: ReadP ClockTime
>>>  			getTime = do
>>>  				  (hour, minutes, seconds) <- readHMS
>>> -				  return (mkClockTime 1970 1 1 hour minutes seconds currTZ)
>>> +				  return (mkClockTime 1970 1 1 hour minutes seconds 0)
>>>  
>>>  	fromSqlValue SqlDate s = f_read getDate s
>>>  		where
>>>  			getDate :: ReadP ClockTime
>>>  			getDate = do
>>>  				  (year, month, day) <- readYMD
>>> -				  return (mkClockTime year month day 0 0 0 currTZ)
>>> +				  return (mkClockTime year month day 0 0 0 0)
>>>  
>>>  	fromSqlValue SqlDateTimeTZ s = f_read getDateTimeTZ s
>>>  		where
>>> @@ -400,7 +389,7 @@
>>>  			getDateTime :: ReadP ClockTime
>>>  			getDateTime = do
>>>  				(year, month, day, hour, minutes, seconds) <- readDateTime
>>> -				return (mkClockTime year month day hour minutes seconds currTZ)
>>> +				return (mkClockTime year month day hour minutes seconds 0)
>>>  
>>>  	fromSqlValue _ _ = Nothing