Windows 10, Cygwin 3.3.3(0.341/5/3), sqlite v3.37.2

SQLite seems to think I am in London, but I'm in Arizona. I am working with DateTime stamps so I wanted to understand them. UTC time should be different than localtime but it's not, from SQLite's point of view.

sqlite> .version
SQLite 3.37.2 2022-01-06 13:25:41 872ba256cbf61d9290b571c0e6d82a20c224ca3ad82971edc46b29818d5d17a0
zlib version 1.2.11
gcc-5.2.0
sqlite> CREATE TABLE datetime_text(
   ...>    d1 text,
   ...>    d2 text
   ...> );
sqlite> SELECT datetime('now');
2022-02-08 22:46:35           --<<<===== incorrect
sqlite> SELECT datetime('now','localtime');
2022-02-08 22:46:50           --<<<===== correct UTC time
sqlite> INSERT INTO datetime_text (d1, d2)
   ...> VALUES(datetime('now'),datetime('now', 'localtime'));
sqlite> SELECT
   ...>         d1,
   ...>         typeof(d1),
   ...>         d2,
   ...>         typeof(d2)
   ...> FROM
   ...>         datetime_text;
2022-02-08 22:47:37|text|2022-02-08 22:47:37|text  -- should be different  
sqlite> .quit

dougf@HPDesktop ~/documents/...
$ date
Tue Feb  8 15:50:36 MST 2022    <<<=== correct time in Arizona

dougf@HPDesktop ~/documents/...
$ date -u
Tue Feb  8 22:50:42 UTC 2022    <<<=== correct UTC time in Arizona

SQLite depends on the operating system to do timezone conversions for it. There are a lot of #ifdefs to deal with this. And Cygwin is not a platform we support or test.

Presumably you built SQLite yourself. Can you bring it up in a debugger and figure out what is going wrong. I suggest setting a breakpoint on the osLocaltime() routine and single stepping through that, to see what is happening. For example, is it using localtime(), localtime_r(), or localtime_s()? And is whichever OS interface it is using the correct one for Cygwin (I don't know) and does it give a reasonable answer?

This would likely indicate that the "platform" "get time of day" functions are not working properly. The likely culprit is Cygwin. All the code for the datetime functions is to be found in date.c and it relies on the Operating System (or, in this case, the Cygwin emulator) to provide correct results.

The functions to "get current time of day" are provided by the OS layer.

Simlarly on WIndows 10:

SQLite version 3.38.0 2022-02-08 19:37:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select datetime('now'), datetime('now', 'localtime');
┌───────────────────────────┬────────────────────────────────┐
│      datetime('now')      │  datetime('now', 'localtime')  │
├───────────────────────────┼────────────────────────────────┤
│ 2022-02-09 00:15:46.698 Z │ 2022-02-08 17:15:46.698 -07:00 │
└───────────────────────────┴────────────────────────────────┘

NB: I have modified the datetime function to always output milliseconds and utc offset.

Windows time reports:

Leap Indicator: 0(no warning)
Stratum: 3 (secondary reference - syncd by (S)NTP)
Precision: -23 (119.209ns per tick)
Root Delay: 0.0645617s
Root Dispersion: 0.0471397s
ReferenceId: 0xC0A80101 (source IP:  192.168.1.1)
Last Successful Sync Time: 2022-02-08 17:04:47
Source: erpro.dessus.com,0x08
Poll Interval: 10 (1024s)

Phase Offset: 0.0000510s
ClockRate: 0.0156248s
State Machine: 2 (Sync)
Time Source Flags: 0 (None)
Server Role: 576 (Reliable Time Service)
Last Sync Error: 0 (The command completed successfully.)
Time since Last Good Sync Time: 721.7446652s

The stratum 2 server shows:

system peer:        time-a-b.nist.gov:123
system peer mode:   client
leap indicator:     00
stratum:            2
log2 precision:     -14
root delay:         64.598
root dispersion:    23.580
reference ID:       132.163.96.1
reference time:     e5ad8556.a58a5c17  Tue, Feb  8 2022 17:12:06.646
system jitter:      0.651369
clock jitter:       1.067
clock wander:       0.009
broadcast delay:    -50.000
symm. auth. delay:  0.000

I did a g..gle search for "cygwin time wrong" and found an immediate answer from 2016: unset TZ! TZ was set to "America/Phoenix". When it's unset, SQLite returns the correct stuff:

sqlite> select datetime('now');
2022-02-09 00:55:08
sqlite> select datetime('now','localtime');
2022-02-08 17:55:18

I can fix this in my bash profile.

Out of interest, you say SQLite is not tested on Cygwin. What "platform" on Windows is it tested on?

What "platform" on Windows is it tested on?

Native Windows exe and ddl files compiled using MSVC, and invoking native Windows OS interfaces.

No. They are compiled with a GCC 5.2.0 compiler. So sayeth the EXE and DLLs when asked what made them.

I'm not sure how to ask those images what created them, so I can only surmise what you are seeing. I can speak to testing however.

One of the test scaffolding systems used for the SQLite project (test/wapptest.tcl and its callees) presents a little checkbox selecting "MSVC" as the build toolset, and it is used as part of the overall testing regime prior to releases. I use it myself, routinely, and can go back to peruse "test.log" content for several past SQLite releases (and versions leading up to them.) In these logs, which exist for over a dozen build option configurations, I can see the "cl" compiler invoked many times, with flags unknown to gcc but familiar to me as CL.EXE's own, and I see no gcc invocations at all. I can and do personally attest that use of the MSVC builds for testing is a regular part of the SQLite pre-release testing process.

From the age of the gcc you report, I think you may be looking at something Richard built himself for a release. (I assume you got "the" images from the download page.) I'm sure you can agree that such an object is poor evidence with which to refute Richard's claim regarding the toolset used for testing.

I would have to grant that he might have spoken more precisely so as to not intimate that his claim was comprehensive. He's a busy person, so his brief assertion, uttered only in a supporting clause of his point about a testing platform, should not be taken as a considered, sweeping statement which excludes anything.

pragma compile_options will tell you the compiler used to build the executable. Just downloaded from the website:

The sqlite3 executable ...

SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma compile_options;
┌─────────────────────────────────┐
│         compile_options         │
├─────────────────────────────────┤
│ ATOMIC_INTRINSICS=1             │
│ COMPILER=gcc-5.2.0              │
│ DEFAULT_AUTOVACUUM              │
│ DEFAULT_CACHE_SIZE=-2000        │
│ DEFAULT_FILE_FORMAT=4           │
│ DEFAULT_JOURNAL_SIZE_LIMIT=-1   │
│ DEFAULT_MMAP_SIZE=0             │
│ DEFAULT_PAGE_SIZE=4096          │
│ DEFAULT_PCACHE_INITSZ=20        │
│ DEFAULT_RECURSIVE_TRIGGERS      │
│ DEFAULT_SECTOR_SIZE=4096        │
│ DEFAULT_SYNCHRONOUS=2           │
│ DEFAULT_WAL_AUTOCHECKPOINT=1000 │
│ DEFAULT_WAL_SYNCHRONOUS=2       │
│ DEFAULT_WORKER_THREADS=0        │
│ ENABLE_BYTECODE_VTAB            │
│ ENABLE_DBPAGE_VTAB              │
│ ENABLE_DBSTAT_VTAB              │
│ ENABLE_EXPLAIN_COMMENTS         │
│ ENABLE_FTS3                     │
│ ENABLE_FTS4                     │
│ ENABLE_FTS5                     │
│ ENABLE_GEOPOLY                  │
│ ENABLE_JSON1                    │
│ ENABLE_MATH_FUNCTIONS           │
│ ENABLE_OFFSET_SQL_FUNC          │
│ ENABLE_PREUPDATE_HOOK           │
│ ENABLE_RTREE                    │
│ ENABLE_SESSION                  │
│ ENABLE_STMTVTAB                 │
│ ENABLE_UNKNOWN_SQL_FUNCTION     │
│ MALLOC_SOFT_LIMIT=1024          │
│ MAX_ATTACHED=10                 │
│ MAX_COLUMN=2000                 │
│ MAX_COMPOUND_SELECT=500         │
│ MAX_DEFAULT_PAGE_SIZE=8192      │
│ MAX_EXPR_DEPTH=1000             │
│ MAX_FUNCTION_ARG=127            │
│ MAX_LENGTH=1000000000           │
│ MAX_LIKE_PATTERN_LENGTH=50000   │
│ MAX_MMAP_SIZE=0x7fff0000        │
│ MAX_PAGE_COUNT=1073741823       │
│ MAX_PAGE_SIZE=65536             │
│ MAX_SQL_LENGTH=1000000000       │
│ MAX_TRIGGER_DEPTH=1000          │
│ MAX_VARIABLE_NUMBER=32766       │
│ MAX_VDBE_OP=250000000           │
│ MAX_WORKER_THREADS=0            │
│ MUTEX_OMIT                      │
│ SYSTEM_MALLOC                   │
│ TEMP_STORE=1                    │
│ THREADSAFE=0                    │
└─────────────────────────────────┘
sqlite>

The 32-bit DLL:

Python 2.7.18 (v2.7.18:8d21aa21f2, Apr 20 2020, 13:19:08) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> db = sqlite3.connect('')
>>> for row in db.execute('pragma compile_options'): print row
...
(u'ATOMIC_INTRINSICS=1',)
(u'COMPILER=gcc-5.2.0',)
(u'DEFAULT_AUTOVACUUM',)
(u'DEFAULT_CACHE_SIZE=-2000',)
(u'DEFAULT_FILE_FORMAT=4',)
(u'DEFAULT_JOURNAL_SIZE_LIMIT=-1',)
(u'DEFAULT_MMAP_SIZE=0',)
(u'DEFAULT_PAGE_SIZE=4096',)
(u'DEFAULT_PCACHE_INITSZ=20',)
(u'DEFAULT_RECURSIVE_TRIGGERS',)
(u'DEFAULT_SECTOR_SIZE=4096',)
(u'DEFAULT_SYNCHRONOUS=2',)
(u'DEFAULT_WAL_AUTOCHECKPOINT=1000',)
(u'DEFAULT_WAL_SYNCHRONOUS=2',)
(u'DEFAULT_WORKER_THREADS=0',)
(u'ENABLE_BYTECODE_VTAB',)
(u'ENABLE_COLUMN_METADATA',)
(u'ENABLE_DBPAGE_VTAB',)
(u'ENABLE_DBSTAT_VTAB',)
(u'ENABLE_FTS3',)
(u'ENABLE_FTS4',)
(u'ENABLE_FTS5',)
(u'ENABLE_GEOPOLY',)
(u'ENABLE_JSON1',)
(u'ENABLE_MATH_FUNCTIONS',)
(u'ENABLE_PREUPDATE_HOOK',)
(u'ENABLE_RTREE',)
(u'ENABLE_SESSION',)
(u'MALLOC_SOFT_LIMIT=1024',)
(u'MAX_ATTACHED=10',)
(u'MAX_COLUMN=2000',)
(u'MAX_COMPOUND_SELECT=500',)
(u'MAX_DEFAULT_PAGE_SIZE=8192',)
(u'MAX_EXPR_DEPTH=1000',)
(u'MAX_FUNCTION_ARG=127',)
(u'MAX_LENGTH=1000000000',)
(u'MAX_LIKE_PATTERN_LENGTH=50000',)
(u'MAX_MMAP_SIZE=0x7fff0000',)
(u'MAX_PAGE_COUNT=1073741823',)
(u'MAX_PAGE_SIZE=65536',)
(u'MAX_SQL_LENGTH=1000000000',)
(u'MAX_TRIGGER_DEPTH=1000',)
(u'MAX_VARIABLE_NUMBER=32766',)
(u'MAX_VDBE_OP=250000000',)
(u'MAX_WORKER_THREADS=8',)
(u'MUTEX_W32',)
(u'SYSTEM_MALLOC',)
(u'TEMP_STORE=1',)
(u'THREADSAFE=1',)
>>>

and the x64 DLL:

Python 3.9.10 (tags/v3.9.10:f2f3f53, Jan 17 2022, 15:14:21) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> db = sqlite3.connect('')
>>> for row in db.execute('pragma compile_options'): print(row)
...
('ATOMIC_INTRINSICS=0',)
('COMPILER=msvc-1500',)
('DEFAULT_AUTOVACUUM',)
('DEFAULT_CACHE_SIZE=-2000',)
('DEFAULT_FILE_FORMAT=4',)
('DEFAULT_JOURNAL_SIZE_LIMIT=-1',)
('DEFAULT_MMAP_SIZE=0',)
('DEFAULT_PAGE_SIZE=4096',)
('DEFAULT_PCACHE_INITSZ=20',)
('DEFAULT_RECURSIVE_TRIGGERS',)
('DEFAULT_SECTOR_SIZE=4096',)
('DEFAULT_SYNCHRONOUS=2',)
('DEFAULT_WAL_AUTOCHECKPOINT=1000',)
('DEFAULT_WAL_SYNCHRONOUS=2',)
('DEFAULT_WORKER_THREADS=0',)
('ENABLE_BYTECODE_VTAB',)
('ENABLE_COLUMN_METADATA',)
('ENABLE_DBPAGE_VTAB',)
('ENABLE_DBSTAT_VTAB',)
('ENABLE_FTS3',)
('ENABLE_FTS4',)
('ENABLE_FTS5',)
('ENABLE_GEOPOLY',)
('ENABLE_JSON1',)
('ENABLE_MATH_FUNCTIONS',)
('ENABLE_PREUPDATE_HOOK',)
('ENABLE_RTREE',)
('ENABLE_SESSION',)
('ENABLE_STMTVTAB',)
('MALLOC_SOFT_LIMIT=1024',)
('MAX_ATTACHED=10',)
('MAX_COLUMN=2000',)
('MAX_COMPOUND_SELECT=500',)
('MAX_DEFAULT_PAGE_SIZE=8192',)
('MAX_EXPR_DEPTH=1000',)
('MAX_FUNCTION_ARG=127',)
('MAX_LENGTH=1000000000',)
('MAX_LIKE_PATTERN_LENGTH=50000',)
('MAX_MMAP_SIZE=0x7fff0000',)
('MAX_PAGE_COUNT=1073741823',)
('MAX_PAGE_SIZE=65536',)
('MAX_SQL_LENGTH=1000000000',)
('MAX_TRIGGER_DEPTH=100',)
('MAX_VARIABLE_NUMBER=32766',)
('MAX_VDBE_OP=250000000',)
('MAX_WORKER_THREADS=8',)
('MUTEX_W32',)
('SYSTEM_MALLOC',)
('TEMP_STORE=1',)
('THREADSAFE=1',)
>>>

This one is built with MSVC. An old version. This probably explains why it is twice the size of the GCC version ...

Ah, I had semi-forgotten that little vein of data, probably because I think of it as "compiler invocation options". But of course, which compiler to use is an option, and useful to record there.

The Windows version is native. It is almost always and without exception that adding "additional layers of complication" causes bad results.

You can compile sqlite3 for Windows out-of-the-box using either the Microsoft Compiler or GCC (MinGW), using either a standard 32-bit or 64-bit memory model.

You can also use other compilers, though they are not supported in the code directly. I have managed to get both Watcom C, Intel C, and IBM C to compile functional code for Windows (it requires a lot of fiddling though).

The Visual Studio Build Tools are free (you do not need the Visual Studio hooey-gooey, which is, in my opinion, a horrid hunk of junk).

You can find the native MinGW/GCC compilers here: https://sourceforge.net/projects/mingw-w64/files/

I use Ray Linn's build of GCC 10.2.0 that you can find here: https://sourceforge.net/projects/mingw-w64/files/Multilib%20Toolchains%28Targetting%20Win32%20and%20Win64%29/ray_linn/

Also here https://winlibs.com/ you can find the current versions of MinGW GCC and Clang for Windows.

Out of interest, you say SQLite is not tested on Cygwin. What "platform" on Windows is it tested on?

(Answering a question putatively posed to Keith:)

When SQLite and its CLI shell are built for the Windows platform, those image(s) call into the Win32 API for the OS services needed to operate. So the "platform" is that API, which is what "Windows apps" always use. (That's what makes then Windows-specific.) They are tested on real Windows machines where the Win32 API is available for .exe and .dll code to call into.

Cygwin has virtually no relation to Windows with respect to the API it exposes to applications which are built for Cygwin. Cygwin is an adapter layer which attempts (with general but incomplete success) to provide a Unix-like API to applications while intermediating the information passing through that set of interfaces so that it can, itself, be a "Windows app", meaning it relies upon the Win32 API.

So the Windows test cases are either mini-apps or ddl using CLI? I have written a couple of major applications using C++ on QT which has a great SQLite interface. The reason I was using Cygwin was to introduce all my versioned application scripts (AutoHotkey) to fossil. Windows tools suck when you need to use sed and awk and cat and ...

sed, awk, and cat (and all the other usual *nix utilities) are available native for Windows and have been since 1996. The ones I have x32, but I am sure that they could be re-built with a 64-bit compiler if x64 was wanted.

See http://gnuwin32.sourceforge.net/ which has source, too.

So the Windows test cases are either mini-apps or ddl using CLI?

That is almost exclusively true. The only GUI involved in testing is in a HTML browser at the far end of sockets, the serving end of which are "CLI" and "NUI" (network user interface or no user interface) programs.

I use sed and cat and many other Unix-originated tools all the time on my Windows puters. But Perl is my go-to choice for tasks that awk and sed were predominantly used for decades ago before Larry Wall gifted us with Perl.