How to get entries from DB depending on timezone?

[eluser]Jelmer[/eluser]

It’s not really any easier to perform date calculations in unix timestamps than it is a datetime field

Your point is assuming he's already using the mysql timestamps isn't it? Which is already better in some ways then saving it seperately (although it might become usefull again if you want to filter at showing times, but hen you should save the times seperatly and the date as timestamps) But even when comparing the UNIX timestamp with the mySQL timestamp, you can still do more calculations with UNIX timestamps in native PHP then with the preformatted mySQL timestamps. Using the date function to convert to readable and the strtotime function to create the timestamps, and calculating with things like strtotime('+1 week').

Also I must admit I like it better to do the work in PHP, instead of within mySQL:

Code:

$start_date = strtotime('2010-04-12 00:00:00 -0500');
$end_date = strtotime('2010-04-12 23:59:59 -0500');
$this->db->from('shows')->where('date >', $start_date)->where('date <', $end_date)->get();

Or for my own timezone (Netherlands instead of EST):

Code:

$start_date = strtotime('2010-04-12 00:00:00 +0100');
$end_date = strtotime('2010-04-12 23:59:59 +0100');
$this->db->from('shows')->where('date >', $start_date)->where('date <', $end_date)->get();

Either way, whichever you like more, it's always better to save the basis using a standarized format - whether it be a UNIX timestamp or a mySQL timestamp. If you need other methods of selecting, like all the shows that are on at primetime during weekdays, consider those your own indexes and create extra columns that are filled automaticly when creating or editing.