How to classify a few time-stamp according to identical days?

Question

I have a table like this:

// Times
+----+-------------+
| id |  timestamp  |   // echo date('d/m/Y', $time)
+----+-------------+
| 1  | 1448460315  |   //  25/11/2015   -- today
| 2  | 1428440265  |   //  07/04/2015
| 3  | 1418160365  |   //  09/12/2014
| 4  | 1448460215  |   //  25/11/2015   -- today
| 5  | 1438440265  |   //  01/08/2015
| 6  | 1438340265  |   //  31/07/2015
| 7  | 1438437265  |   //  01/08/2015
| 8  | 1448370315  |   //  24/11/2015   -- yesterday
| 9  | 1148370315  |   //  23/05/2006
| 10 | 1447870315  |   //  18/11/2015   -- last week ({11-18}/11/2015)
+----+-------------+

Note: All those number in timestamp column are made of time() function using PHP.

Now I want to know, how can I select all rows which are today Or all rows which are yesterday, or last week?* (it should be noted, in MySQL NOW() is the same with time()).*

For example:

// Times - Today
+----+-------------+
| id |  timestamp  |   // echo date('d/m/Y', $time)
+----+-------------+
| 1  | 1448460315  |   //  25/11/2015   -- today
| 4  | 1448460215  |   //  25/11/2015   -- today
+----+-------------+

Store your timestamps as `TIMESTAMP` or `DATETIME` values and use MySQL's built-in [date comparison functions](http://stackoverflow.com/q/3651985/1679849) to do all this work for you instead of faffing about with integers. — r3mainer, Nov 25 '15 at 14:37

score 1 · Answer 1 · answered Nov 25 '15 at 14:43

1

You would need to use the MySQL BETWEEN function

use PHP to get the timestamp from midnight of day to 11:59:59 of day

date_default_timezone_set('America/Chicago');
$day_begins = strtotime(date('Y-m-d 00:00:00', strtotime('today')));
$day_ends = strtotime(date('Y-m-d 11:59:59', strtotime('today')));


-- sql code will look like
SELECT id FROM table WHERE `timestamp` BETWEEN ($day_begins AND $day_ends)

answered Nov 25 '15 at 14:43

Kearney Taaffe

647
8
20

It is not bad. However I don't know it works or not. But anyway thanks. +1 – Shafizadeh Nov 25 '15 at 14:50
are you not wanting the PHP code? You posted this in the PHP section, so, I thought you were wanting how to do this in PHP...if not, my bad. do you have any questions about my code? is there something you don't understand? If I can help you, let me know! – Kearney Taaffe Dec 29 '15 at 08:43

KiwiJuicer · Answer 2 · 2015-11-25T14:52:22.563

0

With something like:

SELECT
  id,
  timestamp
FROM
  table
WHERE
  DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%y-%m-%d') = (DATE_FORMAT(NOW(), '%y-%m-%d') - INTERVAL 1 DAY)

edited Nov 25 '15 at 14:52

answered Nov 25 '15 at 14:36

KiwiJuicer

1,952
14
28

It does not work, I tested it. It always selects nothing ..! – Shafizadeh Nov 25 '15 at 14:43
Please put the WHERE statements in the SELECT to see what values you get. – KiwiJuicer Nov 25 '15 at 14:45
I did again (what you told, putting where in select), and now last column is not Null, it is containing a date: http://s28.postimg.org/q936pf425/ttt.png – Shafizadeh Nov 25 '15 at 14:56
That irritates me. It should be the same formatting. – KiwiJuicer Nov 25 '15 at 15:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/96162/discussion-between-kiwi-juicer-and-shafizadeh). – KiwiJuicer Nov 25 '15 at 15:13

How to classify a few time-stamp according to identical days?

2 Answers2