CODEWITHSUNDEEP
phpmysql
svenkapudija
svenkapudija

Reputation: 5166

MySQL calculating negative value on INT UNSIGNED

I have a start_date_time INT UNSIGNED column in my table.

I have a query like this

... WHERE (g.start_date_time-$currentTime) > 0 ORDER BY (g.start_date_time-$currentTime)

And I get an error Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in.... If I set it to SIGNED of course everything works, but my question is is there a way to keep it UNSIGNED but anyway calculate negative values (I need just calculation, don't need to store them anywhere), or I should redefine my query?

Upvotes: 0

Views: 1504

Answers (2)

lexabug
lexabug

Reputation: 316

I think you should redefine as following

WHERE g.start_date_time > $currentTime ORDER BY g.start_date_time

This will allow using indexes on g.start_date_time column and you don't have to order by (g.start_date_time-$currentTime)

Upvotes: 1

Rob
Rob

Reputation: 12872

You could use CAST() or CONVERT() to cast them to signed integers for your calculation. However, if you have anything larger than a signed integer in there you will get undesired results. You shouldn't be using integers for timestamps anyways. Use a timestamp or datetime field.

Upvotes: 1

Related Questions