Android Question Convert DATE from field TIMESTAMP(6) MariaSQL to long integer

Lakhtin_V

Active Member
Licensed User
Longtime User
I run a request to a web server with the Maria SQL database, in the response I get a field with a date in the TIMESTAMP(6) format. I want to store the value of this field into a variable. But at me it turns out to save only in a string. Then it is not convenient for me to process it. How can I convert the value of TIMESTAMP(6) to a long integer. Parsing the string seems to me not the best. option.
 

DonManfred

Expert
Licensed User
Longtime User
B4X:
DATE_FORMAT(FROM_UNIXTIME(mydatetimefield), '%e %b %Y') AS 'date_formatted'
 
Upvote 0

Lakhtin_V

Active Member
Licensed User
Longtime User
B4X:
DATE_FORMAT(FROM_UNIXTIME(mydatetimefield), '%e %b %Y') AS 'date_formatted'
It is not clear how to use your example in practice, I did not find such a function DATE_FORMAT. After receiving a response to the request from the server, I parse the string. It has a DtTm field

B4X:
elemS.IMEI = m.Get("IMEI")
elemS.DtTm = m.Get("DtTm")
elemS.ID = m.Get("ID")

m.Get("DtTm") -> 2022-11-07 15:34:16.692871
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Snap2.png


B4X:
    db.InitializeAsync("MySQL","com.mysql.jdbc.Driver", "jdbc:mysql://url/dbname?characterEncoding=utf8","user","pw")
    Wait For MySQL_Ready (Success As Boolean)
    If Success Then
        Log($"MySQL_Ready(${Success})"$)
    End If
    Dim qry As String = $"Select img_id,DATE_FORMAT(img_createdon, '%e %b %Y') AS date_formatted FROM beka_attachments LIMIT 0,1;"$
    Dim RS As ResultSet = db.ExecQuery2(qry, Array As String())
    If RS.IsInitialized Then
        If RS.NextRow Then
            'belegid = RS.GetString("order_id")
            Log($"imgID: ${RS.GetString("img_id")}"$)
            Log($"imgTime: ${RS.GetString("date_formatted")}"$)
            RS.Close
        End If
    End If

where img_createdon is my Timestamp-field in my mariasql-Database. I formatted it with %e %b %Y ("2 Nov 2021" in this case)

Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
MySQL_Ready(true)
imgID: 5
imgTime: 2 Nov 2021
 
Last edited:
Upvote 0

Lakhtin_V

Active Member
Licensed User
Longtime User
but you are using a mariadb-Database? My example above is using B4J as a direct connection from Android makes no sense. i am using my database at my provider...
I'm use B4A, how the query string to Maria SQL should look like so that the "Data" TIMESTAMP(6) field in the received response is displayed as a long integer.
DateTime:
            D1=1668079665281
            Ds1=DateTime.DateTimeFormat(D1,"yyyy-MM-dd HH:mm:ss")
or
            D2=1668079995281
            Ds2=DateTime.DateFormat(D1,"yyyy-MM-dd HH:mm:SS")

            strQuery="SELECT * FROM `Datas` WHERE (`Data`>=" & D1 & " AND `Data`<" & D2 & " AND `IMEI`='" & IMEI & "')"
 or
            strQuery="SELECT * FROM `Datas` WHERE (`Data`>=" & Ds1 & " AND `Data`<" & Ds2 & " AND `IMEI`='" & IMEI & "')"
 
Upvote 0
Top