Android Question Format for timestamp key on select

Marco Maria Vilucchi

Active Member
Licensed User
Longtime User
hi all,
I have a MySql table whose key is timestamp.
I have to read for ">".
My code is:
B4X: 
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
 
Sort by date Sort by votes
M

Mahares

Expert
Licensed User
Longtime User
Marco Maria Vilucchi said:
But my result isn't correct and I have records with key<mykey
How have I to format "mykey" to read correctly?
Click to expand...
Try something like this to see if you have any records.:
B4X: 
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.
 
Upvote 0

Marco Maria Vilucchi

Active Member
Licensed User
Longtime User
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....
 
Upvote 0

Marco Maria Vilucchi

Active Member
Licensed User
Longtime User
Here is my code....
But the problem is: How have I to format manmessaggiotime?
co02time is timestamp

B4X: 
'    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)
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Marco Maria Vilucchi said:
co02time is timestamp
Click to expand...
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.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Marco Maria Vilucchi said:
and sure: I need a conversion, but how?
Click to expand...
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.
 
Upvote 0

Marco Maria Vilucchi

Active Member
Licensed User
Longtime User
I try to explain better.... sorry
I have 5 records in the table:
B4X: 
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:

B4X: 
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
B4X: 
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?
 
Upvote 0

udg

Expert
Licensed User
Longtime User
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)
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…