search

tinogomes / mdb-sqlite

Automatically exported from code.google.com/p/mdb-sqlite
BSD 3-Clause "New" or "Revised" License
2 stars 4 forks source link

datatype "date/time" not supported? #1

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago 
i tried your converter to generate voc.sqlite out of voc.mdb
everything worked fine except all fields with date/time data were converted to 
strange integers 
which don't have anything in common with the original data! 
is there a fix for this bug or am I just doing sth. wrong?

in kindly regards, David

Original issue reported on code.google.com by duernwir...@gmail.com on 17 Jul 2008 at 9:11

Attachments:

GoogleCodeExporter commented 8 years ago 
SQLite does not have any support for date/time datatypes -- the dates should be
stored as milliseconds since the epoch:

The number of milliseconds since January 1, 1970, 00:00:00 GMT

Original comment by landon.j.fuller@gmail.com on 23 Jul 2008 at 5:24

GoogleCodeExporter commented 8 years ago 
This should be documented, so accepting this bug to track that work.

Original comment by landon.j.fuller@gmail.com on 23 Jul 2008 at 5:25

GoogleCodeExporter commented 8 years ago 
I have experienced the same issue and I believe it is due to the way sqlite 
handles 
date/time.  The behavior is document on the sqlite website at 
http://www.sqlite.org/lang_datefunc.html 
However, a work-around exists for this issue:
Invoke the sqlite function datetime(...) when populating the table.  So, 
instead of 
the '?' parameter for a SHORT_DATE_TIME type, we need to do something like 
"datetime( 
? / 1000, 'unixepoch')" in the prepared statement that populates the table. 
I have attached a file which implements this.
Hope this helps!

Original comment by dimaof...@gmail.com on 27 Nov 2008 at 12:22

Attachments:

GoogleCodeExporter commented 8 years ago 
I tried your class (thanks) and it gave me 2 hours backward results. can you 
please suggest me a fix to my problem? thanks.

Original comment by avi...@gmail.com on 12 Nov 2010 at 11:34

GoogleCodeExporter commented 8 years ago 
datetime( ? / 1000, 'unixepoch') return the datetime value on UTC timezone. To 
compensate for your local timezone, you have to change the line to 
datetime( ? / 1000, 'unixepoch', 'localtime').

BTW, you have to update sqlitejdbc driver to the latest version in your project 
as well.

Original comment by kash1...@gmail.com on 12 Apr 2012 at 3:19

GoogleCodeExporter commented 8 years ago 
Hi, The DateTime conversion was not working for me.

I have put together a quick and dirty patch that fixes it for my needs. I hope 
it helps someone.
Thanks for the converter!

Original comment by Dr.Pa...@gmail.com on 15 Apr 2012 at 12:52

Attachments:

GoogleCodeExporter commented 8 years ago 
I have created this folk for this bug on github below.
https://github.com/walter426/mdb-sqlite
Pls check it.

Original comment by walterte...@gmail.com on 23 Jan 2014 at 7:46

GoogleCodeExporter commented 8 years ago 
Hi, it would be great if the project downloads (mdb-sqlite-1.0.2.tar.bz2) could 
be updated to include this fix. Thanks.

Original comment by jh.bette...@gmail.com on 23 Nov 2014 at 12:46