Android Question SQLite issue: Datetime.Now

[grafsoft]

Hi,

I cannot save the current date and time to a SQLite table:

    Private t As String
    t = "CREATE TABLE if not exists locations (ID INTEGER PRIMARY KEY, zeit integer, lati real, longi real, " & _
    "alti real, speed real, dist real)"
    sql1.ExecNonQuery (t)

...

    Private dt As Long
    dt=DateTime.now 
    Log ("locationchanged: " & dt)
    sql1.ExecNonQuery ("insert into locations values (NULL, " & dt & "," & Location1.Latitude  & "," & Location1.Longitude & "," & Location1.Altitude & "," & Location1.speed & ",0)")

...

' then I want the "zeit" value from the sql-table:

t = "select * from locations order by ID desc limit 1000"
Private rs As ResultSet
Private z As Long
rs = Tracker.sql1.ExecQuery (t)
Do While rs.NextRow
        ' tim=rs.getstring ("dt")
        z=rs.GetInt ("zeit")
        Log ("Table: " & z)

The log says:

locationchanged: 1643644701339
locationchanged: 1643644702343
** Activity (main) Pause, UserClosed = false **
** Activity (tabelle) Create, isFirst = false **
Table: -1327782010
Table: -1327793008
Table: -1327804006

 

[Alexander Stolte]

sql1.ExecNonQuery ("insert into locations values (NULL, " & dt & "," & Location1.Latitude & "," & Location1.Longitude & "," & Location1.Altitude & "," & Location1.speed & ",0)")

dont put your values into the script directly.
Use this:

sql1.ExecNonQuery2("insert into locations values (NULL,?,?,?,?,?,0",Array As Object(dt,Location1.Latitude,Location1.Longitude,Location1.Altitude,Location1.speed))

and save the datetime as TEXT, not as INT

 

[klaus]

The problem is here:
z = rs.GetInt ("zeit")
DateTime.Now is a Long variable.
When you read the value back, you need to convert it to date or time for display.
With Zeit = DateTime.Time(z)

You may also have a look at the B4X SQLite database booklet, chapter 2.2.9 Date / Time functions.
SQLite has also date functions, but SQLite ticks are the number of seconds since 1970.01.01.
1000 times less than B4X ticks.

 

[grafsoft]

Thanks to you both! That helped