CODEWITHSUNDEEP
db2db2-luw
user6401178
user6401178

Reputation: 183

DB2 timestamp comparison

I have the following table with 4 rows only: enter image description here

When i run the query 

 select * from TABLE9 
     where dateLastChanged < dateLastUploaded

on this table, i expect to see the rows 1 & 4, however i get this: enter image description here

When i run 

 select * from TABLE9 
     where dateLastChanged > dateLastUploaded

i get enter image description here

, and to 

 select * from TABLE9 
     where dateLastChanged <> dateLastUploaded

i get all 4 of it. ... where dateLastChanged = dateLastUploaded gets none.

I tried casting to timestamp-- got the same results:

 select * from TABLE9 
     where timestamp(dateLastChanged) < timestamp(dateLastUploaded)

Whats missing???

my first time with DB2 on a prj. increasingly "loving" it.

Note: I'm aware that i can get around this on the Java side.

//--------------------------

EDIT:

clarifying what i thought was clear in the Q:

How come 

 select * from TABLE9 
     where dateLastChanged < dateLastUploaded

is returning dateLastChanged < dateLastUploaded, dateLastChanged > dateLastUploaded and dateLastChanged = dateLastUploaded.

How does DB2 decide which timestamp is greater, smaller, equal, not equal to one another. the timestamps for row tNumber=bbbbb were created and written at the same time.

//--------------------

EDIT-2

In my original Q - "greater-than/less-than" in the comparison of the timestamps is confused. This (the latter timestamp is greater/not) is easy enough to figure once i get passed the inconsistency i laid out in the Q and the edit right before this.

Upvotes: 0

Views: 6698

Answers (2)

Rastislav
Rastislav

Reputation: 1

in similar context I found out that problem was missing ; at the end of the command. current DB tools need to select part or full selection of command text to execute if ; missing, because without such selection it takes previous line in SQL editor as part of command on current line and it leads often to executing 2 selects at once, which is syntax error without command delimiter...

Upvotes: 0

data_henrik
data_henrik

Reputation: 17118

DB2 uses ISO-defined logic to compare timestamps. It means that for "lower" the earlier date/time is taken, for "greater" the later date/time. For your example DB2 has evaluated everything correctly. To get different results you need to change the values... :)

Upvotes: 1

Related Questions