CODEWITHSUNDEEP
mysql
jtanmay
jtanmay

Reputation: 2647

Select data based on timestamp and interval from mysql

In joomla, it has jos_session table in which time is stored as unixtimestamp in 'time' column. I want to select the records which are older then a week or two days, basically any interval.

Not much hands on DB, but i did tried with date_sub, but it seems taking date as an argument. So I also tried using FROM_UNIXTIME to convert, but nothing seems to be working.

The last query I tried was SELECT username FROM jos_session WHERE DATE_SUB(FROM_UNIXTIME(time,'Y-m-d'), INTERVAL 2 DAY ); But it seems to giving empty set and many warnings!

Can anyone please help!

Thanks in advance, Tanmay

Upvotes: 1

Views: 938

Answers (1)

Ashish
Ashish

Reputation: 68

Try this. It should work:

SELECT username FROM jos_session WHERE TO_DAYS(FROM_UNIXTIME(CAST(timeAS UNSIGNED))) < TO_DAYS(FROM_UNIXTIME(UNIX_TIMESTAMP()))-2

Upvotes: 1

Related Questions