Android Question SQLite issue: Datetime.Now

grafsoft

Well-Known Member
Licensed User
Longtime User
Hi,

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

B4X:
    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

Expert
Licensed User
Longtime User
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:
B4X:
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
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
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.
 
Upvote 1
Cookies are required to use this site. You must accept them to continue using the site. Learn more…