CODEWITHSUNDEEP
mysqldate
user655334
user655334

Reputation: 1015

Get two days data from MySQL table

I have unix timestamp field in the database.

check_in_mktime
-------------
1345639136
1345639146
1345639176

How to get the two days data (including today and yesterday) using timestamp.

Upvotes: 2

Views: 947

Answers (4)

Akash KC
Akash KC

Reputation: 16310

You can do like this: 

 SELECT * 
 FROM your_table 
  WHERE Date(FROM_UNIXTIME(check_in_mktime)) between date(now() + interval 1 day) and date(now() - interval 1 day)

Upvotes: 1

Devart
Devart

Reputation: 122002

Try this query -

(edited)

SELECT * FROM table1
WHERE
  check_in_mktime >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 DAY) AND
  check_in_mktime < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY);

Upvotes: 2

John Woo
John Woo

Reputation: 263813

You need to use the following:

  • NOW() => current date and time
  • FROM_UNIXTIME() => converts timestamp to date
  • DATE() => get date from datetime datatype
  • DATE_SUB() => subtracts date

Try,

  SELECT * 
  FROM  your_table 
  WHERE DATE(FROM_UNIXTIME(check_in_mktime`))     -- converts timestamp to date
                   between DATE(NOW) and                    -- today
                           DATE_SUB(NOW(),INTERVAL 1 DAY)   -- yesterday

Upvotes: 1

xdazz
xdazz

Reputation: 160893

If there is no checkin in future time, then 

SELECT * FROM table_name 
WHERE check_in_mktime >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 DAY)

Upvotes: 0

Related Questions