MYSQL and UNIX timestamps...

[eluser]evolutionxbox[/eluser]
I have a table in which I have a set of events, in each row there is a date (the date when the event is) and these dates are stored as UNIX timestamps.

My question is how can I perform a "like" query to find all events within a certain month, e.g.

Code:

$query = "SELECT * FROM events WHERE date LIKE '2010-01%'";

This won't work with UNIX timestamps (e.g. 1264163400).

Any thoughts?

[eluser]InsiteFX[/eluser]

Code:

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00       |
+---------------------------+

InsiteFX

[eluser]mddd[/eluser]
With LIKE, you are treating the date as a string and comparing against that. So Mysql will compare '1264163400' with '2010-01' and that will not match.
Assuming the database has to stay the same (so you will keep using Unix timestamps) I would do this:

Code:

SELECT * FROM events WHERE YEAR((FROM_UNIXTIME(date))=2010 AND MONTH(FROM_UNIXTIME(date))=1

So, you turn the number into a real date and then use Mysql's date functions to check that date.

But in my opinion it would be even better just to use a Date field (or Datetime if times are important). Then you would not have this issue. You can just as easily compare dates as timestamp integers. So why not use the smartness of Mysql for your date/time needs.

[eluser]evolutionxbox[/eluser]
How will I do this using the active record class... or should I write the query myself?
(Thank you for all your help) =)

[eluser]mddd[/eluser]
I think that is your own preference. Some people like the CI stuff, some like to write SQL their own way.
Using the CI class it would be:

Code:

// $year and $month are the terms you are searching for
$this->db->where('year(from_unixtime(date))', $year, false);
$this->db->where('month(from_unixtime(date))', $month, false);
$result_array = $this->db->get('events')->result_array();

[eluser]evolutionxbox[/eluser]
Thanks, this is much appreciated.

Why the false in $this-db->where()?

[eluser]danmontgomery[/eluser]
Prevents active record from trying to escape field names, which is almost always necessary with complicated queries.

http://ellislab.com/codeigniter/user-gui...ecord.html

[eluser]evolutionxbox[/eluser]
When I use the following code to get a specific day, it gives me the day after...?

Code:

$this->db->where('day(from_unixtime(date))', $day, false);
$this->db->order_by('date');
$query = $this->db->get('events');

The info in the database is unix timestamp for the 24th Jan.
When I put $day=23 it returns with the results for the 24th.

Why is this?

[eluser]evolutionxbox[/eluser]
I have tried again, even stripping down the code it still is wrong. Why?!?!?

[eluser]mddd[/eluser]
There could be time offset issues. A Unix timestamp has no 'time zone' in it. So it could be that you have to compensate for a shift in time zones. You can try this by saving something to your database at a known time and then looking at the date/time you get back when you use from_unixtime. If there is a difference, you'll have to add/subtract a number of hours.

Unix timestamps are nice for comparing to see how long ago something was (in seconds), but less nice for storing exact dates/times. At least that is my opinion.