Re: [sqlite] Bug Report: DateTime incorrect for Windows

[4eanlss]

I would like to propose this version of winCurrentTime() which generates the
same results as the current implementation but much simpler:

int winCurrentTime(sqlite3_vfs *pVfs, double *prNow) {
  FILETIME ft;
  ULARGE_INTEGER ft64;
#if SQLITE_OS_WINCE
  SYSTEMTIME time;
  GetSystemTime();
  /* if SystemTimeToFileTime() fails, it returns zero. */
  if (!SystemTimeToFileTime(,)) {
return 1;
  }
#else
  GetSystemTimeAsFileTime();
#endif
  UNUSED_PARAMETER(pVfs);
  /* FILETIME structure is an unsigned 64-bit value representing the number of
 100-nanosecond intervals since January 1, 1601 (= JD 2305813.5). 

 1000*86400=(2^14)*(5^9)*(3^3)
  will need to add 2305813.5 to get Julian day.
 Will use 2^13 so that (2*2305813.5)=4611627
  can be added in before float computation.
 
 Summary:
  Divide by 2^13 i.e. shift right 2^13
  Add Julian offset 4611627*(5^9)*(3^3) = 0x0DD2E611DD10D
  Divide by remaining factors 2*(5^9)*(3^3) = 105468750
  */
  ft64.u.LowPart = ft.dwLowDateTime;
  ft64.u.HighPart= ft.dwHighDateTime;
  *prNow = ((ft64.QuadPart>>13) + 0x0DD2E611DD10DL) / 105468750.0L;
  return 0;
}

4eanlss


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: DateTime incorrect for Windows

[4eanlss]

Shane Harrelson  writes:
> I'd be worried that other 64 bit calculations are going wrong as well.
> 
> Can you verify what type sqlite3_int64 is being set to in your environment?
> 
> Also can you try the following (note the L suffix on the constants):
> 
> static const sqlite3_int64 max32BitValue =
>  (sqlite3_int64)20L + (sqlite3_int64)20L +
>  (sqlite3_int64)294967296L;
> 
Results not as I expected. Adding L didn't change anything.
However, removing the casts leaving just this:
  20L + 20L + 294967296L;
results in compiler error:
  Numeric constant too large in function winCurrentTime

Then I tried this:
  2L*20L + 294967296L;
No compiler error but still left with a result of zero.

Remaining options which do work (with or without the L) are:
Amazingly: 4294967296L;
or this: 0x01L;
or this: (sqlite3_int64)1<<32; // but only with typecast

4eanlss

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: DateTime incorrect for Windows

[Shane Harrelson]

On Fri, Feb 26, 2010 at 1:49 AM, 4eanlss <4ean...@engineer.com> wrote:

>
> Shane,
>
> I apologize for my quick snap response.
> I have debugged the code and have identified what is happening.
> The calculation in my environment results in 0 (zero) for this:
>  static const sqlite3_int64 max32BitValue =
>  (sqlite3_int64)20 + (sqlite3_int64)20 +
>  (sqlite3_int64)294967296;
>
> Which breaks all of the following datetime calculations.
> When compiled with the type sqlite3_uint64 then it works correctly.
>
> I am working on a much simpler winCurrentTime solution but I'm stilling
> creating all test cases to verify the algorithm before posting.
>
> 4eanlss
>
>
I'd be worried that other 64 bit calculations are going wrong as well.

Can you verify what type sqlite3_int64 is being set to in your environment?

Also can you try the following (note the L suffix on the constants):

static const sqlite3_int64 max32BitValue =
 (sqlite3_int64)20L + (sqlite3_int64)20L +
 (sqlite3_int64)294967296L;

-Shane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: DateTime incorrect for Windows

[4eanlss]

Shane Harrelson  writes:

> ...
> I suspect that one of these "quirks" in your build environment is being
> exposed by the code in winCurrentTime().Perhaps you could debug through
> the code and find where the calculations are going wrong?   In this way we
> could provide more help.
> 

Shane,

I apologize for my quick snap response.
I have debugged the code and have identified what is happening.
The calculation in my environment results in 0 (zero) for this:
  static const sqlite3_int64 max32BitValue = 
  (sqlite3_int64)20 + (sqlite3_int64)20 +
  (sqlite3_int64)294967296;

Which breaks all of the following datetime calculations.
When compiled with the type sqlite3_uint64 then it works correctly.

I am working on a much simpler winCurrentTime solution but I'm stilling
creating all test cases to verify the algorithm before posting.

4eanlss




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: DateTime incorrect for Windows

[Shane Harrelson]

On Tue, Feb 23, 2010 at 1:56 AM, 4eanlss <4ean...@engineer.com> wrote:

> Shane Harrelson  writes:
>
> >
> > I was unable to duplicate your error with the CLI and 3.6.22 (compiled
> with
> > cygwin\gcc or msvc):
> Ok, so Borland not supported and windows API not followed.
> Just as I expected from open source software.
>
>

I never said Borland was not supported.  I said I wasn't able to reproduce
the problem with the two compilers currently available to me.   It would be
very difficult to maintain build environments for every compiler and
compiler version used by SQLite users.
If you check the full source, you'll see where we've made specific
modifications for many compilers not used by us, like the Borland compilers,
all done and tested with the help of users.

I'm not certain what you mean by "windows API not followed".  Could you
provide more information?  In non CE builds, the only Windows API called in
the winCurrentTime() function is GetSystemTimeAsFileTime(). This returns a
FILETIME structure containing two DWORDS (u32s) representing the the number
of 100-nanosecond intervals since January 1, 1601.

The MSDN docs on the Windows API that use and manipulate system time values
state that it will be zero or positive, and less than 0x8000
(except for functions such as SetFileTime() which use 0x to
indicate that the previous file time should be preserved.)   Regardless, an
int64 is large enough to represent all the 100-nanosecond intervals from
January 1, 1601 through sometime in the year 30,828.

We use casting and 64-bit math to convert and manipulate these two DWORDS as
64-bit *signed *integers.   We avoid using uint64s because it's difficult
enough working through all the various compiler quirks out there for
manipulating 64-bit types with just int64s without introducing another
type.   Case in point, the odd initialization of some of the "consts" we use
in the winCurrentTime() function and the use of "math" instead of shifting.

I suspect that one of these "quirks" in your build environment is being
exposed by the code in winCurrentTime().Perhaps you could debug through
the code and find where the calculations are going wrong?   In this way we
could provide more help.

HTH.
-Shane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: DateTime incorrect for Windows

[4eanlss]

Shane Harrelson  writes:

> 
> I was unable to duplicate your error with the CLI and 3.6.22 (compiled with
> cygwin\gcc or msvc):
Ok, so Borland not supported and windows API not followed.
Just as I expected from open source software.





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: DateTime incorrect for Windows

[Shane Harrelson]

I was unable to duplicate your error with the CLI and 3.6.22 (compiled with
cygwin\gcc or msvc):

SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT datetime('now') as NOW;
2010-02-05 16:33:50
sqlite> SELECT datetime('now','utc') as UTC;
2010-02-05 21:33:50
sqlite> SELECT datetime('now','localtime') as MST;
2010-02-05 11:33:50

The date/time functions are described at
http://www.sqlite.org/lang_datefunc.html

It states that the '"utc" assumes that the string to its left is in the
local timezone and adjusts that string to be in UTC' so I think at least the
results for datetime('now','utc') are consistent.

-Shane

On Fri, Feb 5, 2010 at 1:04 AM, <4ean...@engineer.com> wrote:

>
>
>
>  Hello,
>
> I'm compiling the 3.6.21 amalgamation with Borland command-line compiler
> version 5.2 for Windows XP.
> My compilation command is bcc32.exe -5 -H- -O2 -RT- -a8 -x- maintest.c
> sqlite3.c
> where maintest.c is similar to the 
> http://www.sqlite.org/quickstart.htmlsample C code.
>
> The 3 SQL statements executed in maintest.c are:
> SELECT datetime('now') as NOW;
> SELECT datetime('now','utc') as UTC;
> SELECT datetime('now','localtime') as MST;
>
> Current local-time is 22:50 MST.
> The results are:
> NOW = 1601-01-01 00:04:49
> UTC = 1601-01-01 07:04:49
> MST = 1601-01-01 17:04:49
>
> These are not as expected.
>
> If I adjust function winCurrentTime() to use sqlite3_uint64 instead of
> sqlite3_int64 then the math for system time works out correctly.
> I did this by adding option -DSQLITE_FIX_WINTIME=1 to my compiler command
> line and code modifications as show below.
>
> Here are the results with unsigned int64:
> NOW = 2010-02-05 05:51:05
> UTC = 2010-02-05 12:51:05
> MST = 2010-02-04 22:51:05
>
> My compiler's defines are:
> _INTEGRAL_MAX_BITS
> __TLS__
> __FLAT__
> _Windows
> __BORLANDC__
> __CDECL__
> __CONSOLE__
> _MT
> __CGVER__
> __MT__
> __BCOPT__
> _CPPUNWIND
> __TURBO__
> _WIN32
> _M_IX86
> __WIN32__
>
> Here is the code inserted immediately before winCurrentTime() function:
> #if defined(SQLITE_FIX_WINTIME) && SQLITE_FIX_WINTIME
> #define sqlite3_int64 sqlite3_uint64
> #endif
>
> Then the code inserted immediately after winCurrentTime() function:
> #if defined(SQLITE_FIX_WINTIME) && SQLITE_FIX_WINTIME
> #undef sqlite3_int64
> #endif
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: DateTime incorrect for Windows

[Wilson, Ronald]

> The 3 SQL statements executed in maintest.c are:
> SELECT datetime('now') as NOW;
> SELECT datetime('now','utc') as UTC;
> SELECT datetime('now','localtime') as MST;
> 
> Current local-time is 22:50 MST.
> The results are:
> NOW = 1601-01-01 00:04:49
> UTC = 1601-01-01 07:04:49
> MST = 1601-01-01 17:04:49

First off, 

datetime('now') is already UTC.
datetime('now', 'utc') will give you nonsense

With that out of the way, what does the sqlite3 command line return for
these queries?

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division
assuredcommunications(tm)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug Report: DateTime incorrect for Windows

[4eanlss]

 

 Hello,

I'm compiling the 3.6.21 amalgamation with Borland command-line compiler 
version 5.2 for Windows XP.
My compilation command is bcc32.exe -5 -H- -O2 -RT- -a8 -x- maintest.c sqlite3.c
where maintest.c is similar to the http://www.sqlite.org/quickstart.html sample 
C code.

The 3 SQL statements executed in maintest.c are:
SELECT datetime('now') as NOW;
SELECT datetime('now','utc') as UTC;
SELECT datetime('now','localtime') as MST;

Current local-time is 22:50 MST.
The results are:
NOW = 1601-01-01 00:04:49
UTC = 1601-01-01 07:04:49
MST = 1601-01-01 17:04:49

These are not as expected.

If I adjust function winCurrentTime() to use sqlite3_uint64 instead of 
sqlite3_int64 then the math for system time works out correctly.
I did this by adding option -DSQLITE_FIX_WINTIME=1 to my compiler command line 
and code modifications as show below.

Here are the results with unsigned int64:
NOW = 2010-02-05 05:51:05
UTC = 2010-02-05 12:51:05
MST = 2010-02-04 22:51:05

My compiler's defines are:
_INTEGRAL_MAX_BITS
__TLS__
__FLAT__
_Windows
__BORLANDC__
__CDECL__
__CONSOLE__
_MT
__CGVER__
__MT__
__BCOPT__
_CPPUNWIND
__TURBO__
_WIN32
_M_IX86
__WIN32__

Here is the code inserted immediately before winCurrentTime() function:
#if defined(SQLITE_FIX_WINTIME) && SQLITE_FIX_WINTIME
#define sqlite3_int64 sqlite3_uint64
#endif

Then the code inserted immediately after winCurrentTime() function:
#if defined(SQLITE_FIX_WINTIME) && SQLITE_FIX_WINTIME
#undef sqlite3_int64
#endif



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users