MYSQL TIMESTAMPDIFF function is not working

Question

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.

It's a good thing to provide a fiddle. But you should always include everything necessary to reproduce the problem in the question itself, not only in a fiddle. The fiddle possibly gets down and then the question is useless. — sticky bit, Aug 03 '19 at 17:22
@stickybit Thanks i ll update it here also. Can you please look at the problem its really a long time since this is giving me problem — MR_AMDEV, Aug 03 '19 at 17:24
You're inserting four rows where for each row just one column is filled, so there is no row where both `timeperiod` and `timeperiod_and` are filled. And `timeperiod` and `timeperiod_exp` are `integer`s, not `timestamp`s. — sticky bit, Aug 03 '19 at 17:24
Remove the WHERE clause and [see what you get](https://www.db-fiddle.com/f/enVQ67m37VPLsMAZDWNcjx/0). Then go and read about [INSERT Syntax](https://dev.mysql.com/doc/refman/8.0/en/insert.html) — Paul Spiegel, Aug 03 '19 at 17:26
i have got it the insert syntax i am using fiddle for the first time ever thanks for pointing that out and check the updated fiddle now — MR_AMDEV, Aug 03 '19 at 17:28

score 1 · Accepted Answer · answered Aug 03 '19 at 17:47

1

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

answered Aug 03 '19 at 17:47

Paul Spiegel

30,925
5
44
53

Thanks for that info it was making an headache. – MR_AMDEV Aug 03 '19 at 17:49
One question how can i select record where timestamp is not between today? – MR_AMDEV Aug 03 '19 at 17:50
1

"not between today" - what does that mean? Probably `DATE(FROM_UNIXTIME(timeperiod)) <> CURRENT_DATE()`. – Paul Spiegel Aug 03 '19 at 17:53

score 0 · Answer 2 · answered Aug 03 '19 at 17:43

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

MYSQL TIMESTAMPDIFF function is not working

2 Answers2