• 0

[MYSQL] Can't subtract unix timestamp


Question

I have been trying to write a MySQL query recently in which I subtract the current system timestamp from a timestamp stored in a column in one of my MySQL tables.

Here is the query I used:

 
SELECT ID_MEMBER, date, UNIX_TIMESTAMP( ) AS now, date - UNIX_TIMESTAMP( ) AS timeleft
FROM items AS a
WHERE ID_ITEM =1

Here is the output I got after running the above query in phpmyadmin:

sqtswh.png

So why does it give:

1266017133 - 1277569539 = 18446744073697999210 ?

Note: I only get this problem when the answer is a negative value, as with the above example.

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

Hmmm... Sorry, but I am not terribly experienced with this, but it is working for me in a quick temporary setup.

I can get a negative value by default. :unsure:

post-36818-12775734121412.png

EDIT: For what it is worth, I am doing this *on* a unix (Linux, technically) machine...

Edited by markjensen
Link to comment
Share on other sites

  • 0

date is probably unsigned so MySQL is keeping the result as unsigned and so going hugely positive as opposed to negative in value. Are you sure you don't want UNIX_TIMESTAMP() - date instead?

Link to comment
Share on other sites

  • 0

You're right, date is unsigned. Is there a way to let the result be signed? And I really need to used 'date - UNIX_TIMESTAMP( )' , since with my 'date' values the result is sometimes positive and sometimes negative.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.