Android Question SQlite and date problem

[marcick]

I have a DB where I store records with a TEXT field that contain a date, in the format Year-month-day Hours:minutes:seconds.

For example now would be 2016-10-25 12:56:00

For some reasons (still have to discover where is the bug) sometimes the record is stored with a missing zero.

For example 2016-06-17 05:50:43 is stored as 2016-6-17 05:50:43

When it happens, I have troubles in sorting the records by date with the query:

SELECT * FROM records ORDER BY StoreDate DESC

The resul is like this (first field is the primary key):

12530,2016-6-17 05:50:43
29663,2016-07-23 17:02:13
29662,2016-07-23 17:01:59
29661,2016-07-23 17:01:27
29660,2016-07-23 17:00:57

The first record of the result is of june 17th (should be 2016-06-17) but always appear as the most recent in the list

Anybody is able to explain this ?

 

[Erel]

Why aren't you storing the date as ticks? It will make it simpler.

Where is the code that inserts the data to the database?

 

[marcick]

Why aren't you storing the date as ticks? It will make it simpler.

Where is the code that inserts the data to the database?

Yes, I'll keep in mind for the future, but can't modify the structure at this point.
The only point where I potentially see the bug is this:

I have integer representations of year, month, day etc
At some point they are converted in HEX with this routine

Sub DecToHex (bt As Int) As String
    Dim bc As ByteConverter
    Return bc.HexFromBytes(Array As Byte(bt))
End Sub

For example June is 6 decimal and become 06 in HEX

Is that possible that sometine (only sometime) the HexFromBytes functions return 6 (HEX) instead of 06, without the leading zero ?

 

[klaus]

You can store dates as ticks in the database and get text dates back with the SQLite date query like:
Examples of queries with Ticks = 1448795854111 (B4A ticks):
SQL1.ExecQuerySingleResult("SELECT date(" & (Ticks / 1000) & " , 'unixepoch')")
Returns: 2015-11-29
SQL1.ExecQuerySingleResult("SELECT time(" & (Ticks / 1000) & " , 'unixepoch')")
Returns: 11:17:34
SQL1.ExecQuerySingleResult("SELECT datetime(" & (Ticks / 1000) & " , 'unixepoch')")
Returns: 2015-11-29 11:17:34

B4A ticks are the number of milliseconds since January 1, 1970.
SQLite ticks are the number of seconds since January 1, 1970.
That's the reason for (Ticks / 1000).

This is explained in the B4A User's Guide chapter 5.1.9 Date / Time functions.

 

[marcick]

Thanks Klaus, very interesting.
Actually I want to find the bug so I hope somebody can comment my previous post.

 

[afields]

Thanks Klaus, very interesting.
Actually I want to find the bug so I hope somebody can comment my previous post.

i think that you'll have to go to the sqlite site and specially the functions. With sql you can do a lot of pre-work resulting in a much more quickly programming in b4a ( or any other language).
so, this sql command hope that will help you:
SELECT * FROM records ORDER BY date(Storedate,"yyyy-mm-dd") DESC

here you use a sqlite function date which has as 1 parameter a string date and opcional as secund parameter a string representing the way you want to see the date.

 

[LucaMs]

so, this sql command hope that will help you:
SELECT * FROM records ORDER BY date(Storedate,"yyyy-mm-dd") DESC

Are you sure that your code above works with any DBMS? I'm not. Each DBMS has its own functions, in particular with regard to dates.

Is that possible that sometine (only sometime) the HexFromBytes functions return 6 (HEX) instead of 06, without the leading zero ?

I've not tried but... do you use that function only for months? If so, what do you do after?

Where is the code that inserts the data to the database?

Also, to know if that function works correctly, just write a test, a simple for next 1 to 12 and see the results

 

[marcick]

Thanks everybody but it's difficult to describe all the platform that involve IOS, Android and a server with B4J and MySql.
The point is that sometimes (very rarely but happens) i found a record in Sqiite that has the problem in the date as described in the first post.
A part of a field that should contain "06" for example, just contain "6".
That record looks as the most recent but it isn't: due to the missing "0", the sorting fails.
So I'm trying to find where can be the bug.
Because at some point I use the function HexFromBytes, I wonder if it is possible that sometimes it fails and doesn't add the leading zero.

 

[LucaMs]

Ah, ma... sei italiano? (forse lo sapevo ma la memoria...!)

Sei tu che salvi le date in quel DB, giusto? Quindi spetta a te la costruzione della stringa che rappresenta la data.
1) Non puoi postare il codice che costruisce la stringa?
2) A che ti serve un esadecimale per costruirla?
3) è chiaro che quella funzione restituirà valori coerenti; se hai dei dubbi, crea un piccolo progetto di prova per testarla.


You create and save the data string, right? So:
1) can you post the code you use to create that string?
2) Why do you need hexadecimals to do it?
3) if you have doubts about that function (DecToHex) test it in a separate little project.

 

[marcick]

Thanks again everybody (ciao Luca ..) but it's difficult to explain why when and where I'm using that function.
Data are manipulated many times (Dec, Hex, string) and finally are inserted into Sqlite.
But I have sometime a missing leading "0" so need to find the bug.
The only suspect I have now is about the function HexFromBytes of ByteConverter (iRandomAccessFile library)
I'm sorry that I started with one question about Sqlite but thinking and thinking I'm here to talk about other, but the question now would be:


Is that possible that sometine (only sometime) the HexFromBytes functions with integer 6 as input return "6" (HEX) instead of "06", without the leading zero ?

 

[Mahares]

Is that possible that sometine (only sometime) the HexFromBytes functions with integer 6 as input return "6" (

Why fight the potential bug. Can't you just validate it with this:

Dim myhr As String =DecToHex(6)
    If myhr.Length=1 Then myhr= "0" & myhr

 

[marcick]

Yes, sure ;-)
would be better to know before if there exists a potential bug or not .....
Thanks