Android Question Format for timestamp key on select

[Marco Maria Vilucchi]

hi all,
I have a MySql table whose key is timestamp.
I have to read for ">".
My code is:

mykey="2019-01-10 18:11:23"
qry="Select * FROM mytable where tablekey>mykey

tablekey have timestamp format.

But my result isn't correct and I have records with key<mykey
How have I to format "mykey" to read correctly?
Thanks
Marcom

 

[Mahares]

But my result isn't correct and I have records with key<mykey
How have I to format "mykey" to read correctly?

Try something like this to see if you have any records.:

Dim cursor1 As Cursor
    Dim mykey As String="2019-01-10 18:11:23"
    Dim qry As String ="SELECT * FROM mytable WHERE tablekey > ?"
    cursor1=Starter.SQL1.ExecQuery2(qry, Array As String(mykey))
    Log(cursor1.RowCount)

If 0 records, then try: tablekey < = ?
If still 0 records, then post more code and your database.

 

[Marco Maria Vilucchi]

I know whitch records I need, but this call give me other records.....
I think I need to format mykey in some way, but I don't konw how....

 

[Mahares]

I think I need to format mykey in some way, but I don't konw how....

Why don't you show us your exact code and some of the data in the table. You may get some help

 

[Marco Maria Vilucchi]

Here is my code....
But the problem is: How have I to format manmessaggiotime?
co02time is timestamp

'    leggo i messaggi
    Dim manmessaggiotime as string
    manmessaggiotime="2019-01-11 18:02:34"
    Dim jobx As HttpJob
    Main.Qry="Select * FROM co02messaggi where co02letto=0 and (co02userda=" & Main.manutentekey & " and co02usera=" & Main.nickdestinatariokey & ") or (co02userda=" & Main.nickdestinatariokey & " and co02usera=" & Main.manutentekey & ") and co02time>'" & Main.manmessaggiotime &"' order by co02time"
    messaggio="Resume Qry " & Main.qry
    Log(messaggio)
    jobx.Initialize("Job1", Me)
    jobx.PostString(Main.ServerUrl, Main.Qry)

 

[Mahares]

co02time is timestamp

You need to show us what co02time column data looks like in the table. If it is the same format as
2019-01-11 18:02:34, you will have no problem getting the query to work, but if it is in a different format as
manmessaggiotime, then you cannot make the comparison as is.

 

[Marco Maria Vilucchi]

2019-01-11 18:02:34
but I have problems....
and sure: I need a conversion, but how?

 

[Mahares]

and sure: I need a conversion, but how?

If you have a timestamp column data in your table like this: 2019-01-11 18:02:34 and you want to compare it to a string: 2019-01-07 19:42:34, then your timestamp is BIGGER than the string you are comparing it to. There is no CONVERSION needed in this case.
2019-01-11 18:02:34 is bigger than 2019-01-07 19:42:34
It is very hard getting information from you to be able to help you intelligently.

 

[Marco Maria Vilucchi]

I try to explain better.... sorry
I have 5 records in the table:

co02code(timestamp)         co02userda(int) co02usera(int)  co02letto(int)
2019-01-02 18:00:02               8                    7                       0
2019-01-03 17:08:27               7                    8                       0
2019-01-04 11:01:35               8                    7                       0
2019-01-10 07:11:20               7                    8                       0
2019-01-11 08:18:59               8                    7                       0

Thi si may call:

Select * FROM co02messaggi where co02letto=0 and (co02userda=7 and co02usera=8) or (co02userda=8 and co02usera=7) and co02time>'2019-01-11 08:18:59' order by co02time

This is the result

co02code                          co02userda      co02usera     co02letto
2019-01-03 17:08:27               7                    8                  0
2019-01-10 07:11:20               7                    8                  0

where am I wrong?

 

[udg]

Try making use of some paretheses in your query.
I think that your intended query is:

where (co02letto=0) and ((co02userda=7 and co02usera=8) or (co02userda=8 and co02usera=7)) and (co02time>'2019-01-11 08:18:59')

in other words three check blocks linked by AND opeartor, where the second one is made up of two check blocks under the OR operator.

To write your query without parentheses and to understand why your original quaery couldn't work, search for logical operators precedence (generally, not-and-or)

 

[Marco Maria Vilucchi]

Yes I just see it....
sorry..... saolved