MYSQL and UNIX timestamps...

[eluser]evolutionxbox[/eluser]
That does make sense, I have tried it out... it is a whole hour ahead for it's set to GMT, I am on British Summer Time(BST) right now.

I wonder, would I be able to set the unix timezone in the config file?

[eluser]mddd[/eluser]
Both Mysql and Php can be set to the correct timezone. I think this is something you should set in server ini files, not in Code Igniter.

[eluser]evolutionxbox[/eluser]
What if I don't have access to these files?

[eluser]mddd[/eluser]
Both Php and Mysql allow setting these values live. You could make a function in one of your models to do so.
But first, I would check to see where the problem lies exactly. Save some stuff to the database. Check to see what date/time you get back when you read it again.

[eluser]evolutionxbox[/eluser]
I have put the value '1270159200' which is 2010-04-02 12:00AM (YYYY:MMD HH:MM).
Using the from_unixtime() it returns 2010-04-01 11:00AM.

As far as I know that is -1 hour. I am currently on BST not GMT/UTC. Would that make a difference?

[eluser]mddd[/eluser]
Alright, so now you now the problem. Yes, sure you could try that.
But still it would be better just to store a time/date value instead of a unixtime value in the database. If you store '2010-04-01 11:00:00' you will always get the same value back, eliminating the problem.

[eluser]evolutionxbox[/eluser]
The reason I was using unixtime in the database was because I could use a plethora of functions to use it in many different ways.

Using a string seems restrictive (I can't use mdate() to format it), would you say that's true or not?

[eluser]mddd[/eluser]
I agree that you shouldn't use a string. But the Mysql date/time types are not strings. You SEE them as strings, but Mysql stores them as dates. You can do all kinds of calculations with them. And if you would like to get the full date in text, you can do SELECT DATE_FORMAT(date,'%d %M %Y'). Of course you can do such things in php too, but why not select the information directly from Mysql and you don't have to worry about all of that.

So, if you store stuff that is a date or date+time, just use the functions that are available for it!

[eluser]evolutionxbox[/eluser]
Handy. I'll try it. =)

[eluser]evolutionxbox[/eluser]
Using mysql date/time types how do you select the day/month you want?

Maybe...

Code:

$this->db->where('date', $year.'-'.$month.'-'.$day);
$this->db->order_by('date');
$query = $this->db->get('event_list');