CODEWITHSUNDEEP
mysqlsqldatabasetimestampdiff
MR_AMDEV
MR_AMDEV

Reputation: 1942

MYSQL TIMESTAMPDIFF function is not working

I am trying to use TIMESTAMPDIFF function in one of my queries and is making an headache for me.

TABLE

id  |  web_address    |  timeperiod  |  timeperiod_exp

1   |  www.google.com |  1564692614  |  1564779014

1564692614 = GMT: Thursday, August 1, 2019 8:50:14 PM

1564779014 = GMT: Friday, August 2, 2019 8:50:14 PM

WHATS THE PROBLEM ?

As one can see the difference between these two timestamps is exactly 1 day but is returning no records.

SELECT * FROM mytable WHERE TIMESTAMPDIFF(DAY, timeperiod, timeperiod_exp) >= 1

WHERE IS THE FIDDLE ?

https://www.db-fiddle.com/f/udmrC2xdvrEEKGxEF7ty84/7

WHAT SHOULD BE DONE ?

Please take a look at the fiddle above and suggest what should be modified or other function in place of timestampdiff.

Upvotes: 2

Views: 6701

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31832

Look at the documentation for TIMESTAMPDIFF()

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime

As you see, it expects the parameters to be of type DATE or DATETIME. But you have your timestamps stored as integers.

So either use FROM_UNIXTIME() to convert your integer timestamps to DATETIME:

SELECT *
FROM mytable
WHERE TIMESTAMPDIFF(DAY, FROM_UNIXTIME(timeperiod), FROM_UNIXTIME(timeperiod_exp)) >= 1

db-fiddle

Or just use simple arithmetics (since we know how many seconds are in one day):

SELECT *
FROM mytable
WHERE (timeperiod_exp - timeperiod) >= 60*60*24

db-fiddle

Upvotes: 1

MR_AMDEV
MR_AMDEV

Reputation: 1942

As if i see the function TIMESTAMPDIFF() should take two timestamps but it is taking dates instead of direct timestamps in integers Thus the following works:

SELECT * FROM mytable WHERE TIMESTAMPDIFF(DAY, FROM_UNIXTIME(timeperiod), FROM_UNIXTIME(timeperiod_exp)) >= 1

Updated Fiddle

https://www.db-fiddle.com/f/udmrC2xdvrEEKGxEF7ty84/8

Upvotes: 0

Related Questions