XBase (DBF) Date-Format
 

Good morning.

Can someone explain the difference in the storage-formats for calendar-dates in SQLite and XBase DBF?

I am currently writing a converter sqlite2dbase and use at least one “library” which does not handle dates. Something is working alright in LibreOffice Calc when I do not convert the numerical (Integer-) values of the SQLite dates, but in other contexts, these dates are invalid, or the numbers are...

This does not only look like I knew nothing.

Not sure I understand where LO Calc comes into this, but if you export a date from both databases can you not simply compare the 2 to see the differences?

I can and it tells me that they are different. The date from SQlite corresponds to a big number, the one from a DBF-file is smaller. I guessed that one uses the Unix epoch, the other the Lotus epoch but this does not explain the difference. Unfortunately, I face another problem upon converting SQLite to DBF, when I encounter some specific calendar dates:
1431595627281000 is a date from SQLite (places.sqlite, something from the firefox directory).

Dates from the cookies.sqlite (also firefox) are for example 1478478127, 1541334209, etc.
LibreOffice Calc does not show them as valid dates, but at least tries to format them correctly. For example 19/09/-17479, 02/04/-3277

I still must convert the values to a dBase date format, change the endianness or something, but have no idea, how to procede.

In xBase the date is always YYYYMMDD irrespective of the display format.

dBase format.(AFAIR)
First 16 bytes - file header.
Then 16 bytes for each field descriptor.
Then the records
--- For each record, add a byte for the delete mark
Finally an ending <ctrl+z>

Memo field data is stored separately. Maybe the size in the main file was 10 bytes.
OK

Maybe the trick is to use the gem to extract the data from sqlite and then convert it to YYYYMMDD HHSS type of format and then upload that to the DBF? If you have included both gems for each DB
this should be a relatively simple process.

I use SQlite3 and SHP for the conversion. SHP does not support date-formats and the DBF gem would write nothing at all. ;)
Thank you for your hints, Grail and AnanthaP. I will try that and come back with a possible result.

Here is a preliminary first release of the sqlite2dbf program: https://rubygems.org/gems/sqlite2dbf
A call in the German or French locale uses different parameters, -q in German is -s in English (source).

Multi-table databases are converted, too.

The Exception is caught and null-values replace non-available fields. The dates are thus either not converted or omitted.., I guess. But this will hopefully be rectified in a next release. I am already quite content with the result. The task “sounds” so complicated but turns out to be feasible.

More than I wanted to know about SQLite-Dates:
https://www.tutorialspoint.com/sqlit...data_types.htm
https://www.tutorialspoint.com/sqlit..._date_time.htm

In SQLite Text, Real or Integer-fields may contain calendar-dates (and times) and two different epochs are possible. This probably means, that the user of my converter must know which field is a date and also name the epoch or just check in case of a Real type. Else I will have too try for agglomerations of 'reasonable dates' in any column to determine a probable date-field...

Is it just me, or has again something been artificially complicated for lack of foresight? Like the missing char data-type in Ruby, that they had felt necessary to add, later.

Just thought this might be something you might be interested in :- https://github.com/bbatsov/ruby-style-guide

Haven't had a chance to test the converter yet but will let you know :)

ummm ... not to be picky, after the gem is installed, should I be able to run the file from bin directory as you have demonstrated?

I get the following when I do:

Had to push two other versions with fixes for much the same kind of problem. I am on a different computer now, only to test if the downloaded gem will finally do the job.

SORRY. In the meantime, I am preparing a new version anyway, which lets the user name fields with calendar-dates. But I will let you know, probably today, if the available gem is functional. And the Internet-connection is crappy.

Thanks for the style-guide. I tend to dislike it but have to re-read it at occasions.

Version 0.1.6 appears to be functional with Ruby 2.3+.

And... version 0.1.7 provides two new options --dates for date/datetime-values and --time for timestamps. I also handle BLOBS from SQLite as text in dBase because do not see an alternative.

And the new usage message:

Some more feedback information for you:
Looking at the code, I think this issue is a missing 'upcase' to match the keys in tmap. If you fix this one I believe there may be others ;)

This is so cool. 8)

Thank you grail for the effort you put into the test. It had not been my idea to occupy forum-members (to this point) like this, but I can think of more situations, than before, where this “utility” can be nice to have.

And this error reminds me that the transform() method could benefit from eliminating the mapping work, altogether. It will only simplify the code, though... and facilitate tracing such errors, as the one you found!

Okay, I hope that I can correct the bug this evening, but am not sure that I will have the time. Tomorrow, latest.

Edit: Done, 0.1.8. More changes: give a path-name with the --orig parameter, so that files can however be written in arbitrary directories.
Edit 2: I do adapt some of the code to the style-guide referenced above by grail, but will not follow all the recommendations given there. Coming from other languages, my coding style is the one that helps *me* develop, first of all.

Thought I would throw this in here as other question is about options rather than the dates themselves :)

I did a little testing on my own cookies.sqlite and came up with the following, for each field designated as an 'integer' I performed a strftime on the number to test if the date returned was equal to
19700101 and if so, determined that this was an actual integer and not a date, otherwise it would return an actual date which could then be easily stored in your new format.

I guess this could potentially come unstuck if the creator is actually storing large numbers which when converted may be closer to real dates, but thought I would let you know as a possible alternate solution.
I am a little surprised that a database in such high use these days does not have some way to test a field with an 'isdate' type of function if they choose not to store the data in a format easily identified.

ADDENDUM: Seems I might have jumped the gun. My solution was working but I found that the lastAccessed and creationTime, which I would assume by the names should be dates, both give me very odd date information although the time looks ok, examples: