Date field in SQLite

[tdocs2]

Greetings and best wishes to all. Thank you for your help.

Another question from newbie....

There seems to be a conflict (probably my lack of understanding) between the B4A doc and the SQLite definitions of how a date field is stored.

Date fields in B4a are generated as Unix time (see Core def below) but the documentation indicates that it is too large to be stored in an Int field (see below) - it must be stored as long. The documentation for SQLite indicates that it should be stored as an Int (it could also be stored as text, but I guess I am trying to save room and there seems to be an advantage in storing the date in native form, i.e., in Unix format).

QUESTION
When I create a SQLite DB table in B4A, how should I define the date field - int, long, and definitely not float (this would create a non Unix format which DateTime would not support). Would long be converted to Int in SQLite?


Documentation quote from Core and SQLite follows...

Core Library Definition
DateTime
Date and time related methods.
DateTime is a predefined object. You should not declare it yourself.
Date and time values are stored as ticks. Ticks are the number of milliseconds since January 1, 1970 00:00:00 UTC.
This value is too large to be stored in an Int variable. It should only be stored in a Long variable.

SQLite Definition
1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
•TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
•REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
•INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Datatypes In SQLite Version 3

 

[Eduard]

I use

update_timestamp INT NOT NULL DEFAULT 0

to store ticks.

From your website:

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

SQL lite uses only the bytes needed, so INT is fine.

Note: INT is normally only 4 bytes. 8 bytes is big enough for milliseconds timestamps. 256^8 milliseconds is about 0,5 billion years.

 

[citywest]

Ahh the old date and time conundrum!

There are a few answers to this question. As pointed out INT's are fine.

However if you wish to exchange dates-times with other databases and systems then ISO standard is best as it makes any transformations or casts trivial. Most things understand ISO style dates whilst ticks can be an issue.

If so you can define the SQLite field like:

SQL.ExecNonQuery("CREATE TABLE IF NOT EXISTS tbl_mytable (type TEXT,  recordedDateTime DATE, .....)")

Then to update/insert such a field:

Update tbl_mytable Set recordedDateTime = '2012-12-19 01:20:35'

Cheers,

Mark S.

 

[tdocs2]

Thank you

Thank you, Mark and Eduoard.

I still see a conflict in the documentation. B4A tells me use long. SQLite tells me Int.

I understand your point regarding using text trying for ISO...

 

[mc73]

You can use 'integer' in the sqLite table creation process, while getting the field in a 'long' format in b4a.

 

[tdocs2]

Thank you

Thank you, MC.

I would like this solution. However, I ran a small test

    Dim a As Int
    Dim b As Long
    b=DateTime.Now
    a=b
    Log("a="&a)
    Log("b="&b)

Logs
a=-1278615041
b=1355931050495

I do not know if B4A will do the same on a SQLite....

 

[klaus]

A Long variable in B4A is an 8 byte integer.
According to the SQLite documentation as Eduard aleady showed:
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

So you can use Long in B4A and INTEGER in SQLite without any problem.

Best regards.

 

[tdocs2]

Thank u Klaus

Thank you, Klaus.

Best regards.

Sandy

 

[mc73]

Thank you, MC.

I would like this solution. However, I ran a small test

    Dim a As Int
    Dim b As Long
    b=DateTime.Now
    a=b
    Log("a="&a)
    Log("b="&b)

Logs
a=-1278615041
b=1355931050495

I do not know if B4A will do the same on a SQLite....

This is very logical. You shouldn't worry how b4a treats integers but how sqlite treats them. Dim your date as long, let sqlite do the rest

 

[tdocs2]

Thank you

Thank you, MC, for the reassurance.

Best regards.

Sandy

 

[aklisiewicz]

I have an PHP application and it connects to SQLite. The table has a date stored as DATETIME. For some reason I get an error when trying to update a record. I want this SQLite table to be compatybile with MySQL as I plan synchronization in a future and for that reason using either LONG or INT would not be appropriate. I wonder is somebody finally got into the solution to use DATETIME format in an Android App ?

Arthur

 

[citywest]

Arthur,

The simple answer is to use ISO style dates and setup your query string correctly i.e.

Today's date and time looks like 2013-05-30 01:10:60

Your query string should look like {ts '2013-05-30 01:10:60'}
If you are only interested in dates then it will look like {d '2013-05-30'}

This will work with all the DB's I use (MySql, SQLite, MS SQL)

If you wish you can add localization:

2013-05-30T01:10:60.000+1000 (I'm 10 hours in front of GMT)
So query string is {ts '2013-05-30T01:10:60.000+1000'}

This way you can also browse a table and see readable dates rather than numbers, much easier all round.

Cheers,

Mark S.

 

[LucaMs]

I used the format yyyy/mm/dd for fields of type date, as I had no need of the time.

Now that I have this need, I was thinking about using ticks (INTEGER); but if I wanted to filter the data for a given month?

Perhaps it is better to use two TEXT fields, one for the date and one for the time?

 

[Hansbck]

Greetings and best wishes to all. Thank you for your help.

Another question from newbie....

There seems to be a conflict (probably my lack of understanding) between the B4A doc and the SQLite definitions of how a date field is stored.

Date fields in B4a are generated as Unix time (see Core def below) but the documentation indicates that it is too large to be stored in an Int field (see below) - it must be stored as long. The documentation for SQLite indicates that it should be stored as an Int (it could also be stored as text, but I guess I am trying to save room and there seems to be an advantage in storing the date in native form, i.e., in Unix format).

QUESTION
When I create a SQLite DB table in B4A, how should I define the date field - int, long, and definitely not float (this would create a non Unix format which DateTime would not support). Would long be converted to Int in SQLite?


Documentation quote from Core and SQLite follows...

Core Library Definition
DateTime
Date and time related methods.
DateTime is a predefined object. You should not declare it yourself.
Date and time values are stored as ticks. Ticks are the number of milliseconds since January 1, 1970 00:00:00 UTC.
This value is too large to be stored in an Int variable. It should only be stored in a Long variable.

SQLite Definition
1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
•TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
•REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
•INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Datatypes In SQLite Version 3

Use Datatype DATE as sql requires (example: ddate, DATE). The Date must be in the format "YYYY-MM-DD" (also as Sql requires). If an error is encountered it places a date in there that is the starting date of start of development. The date is easy converted or formated in Basic. When you use anything else discussed here, you can not index it on the date field, nor can you do date manipulations (add/subtract to/from date, sort by date or use a date range). If you have any other problems let me know.

 

[LucaMs]

Thanks, I found a solution:

Query = "SELECT strftime('%m', datetime(MyDateField/1000, 'unixepoch')) AS Month FROM MyTable"

 

[LucaMs]

Thanks, I found a solution:

Query = "SELECT strftime('%m', datetime(MyDateField/1000, 'unixepoch')) AS Month FROM MyTable"

More precisely, to filter by month:

Private Query As String = "SELECT * FROM MyTable WHERE strftime('%m', datetime(MyDateField/1000, 'unixepoch')) = '" & strFilterMonth & "'"

Where strFilterMonth must be "09", for example.