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

[Lakhtin_V]

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]

DATE_FORMAT(FROM_UNIXTIME(mydatetimefield), '%e %b %Y') AS 'date_formatted'

 

[Lakhtin_V]

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

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

 

[DonManfred]

    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

 

[DonManfred]

I did not find such a function DATE_FORMAT.

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...

 

[Lakhtin_V]

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.

            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 & "')"

 

[DonManfred]

1. Learn to use parametrized Queries
2. Upload a small project showing the problem.