Best way to store the time and date

[eluser]JamieBarton[/eluser]
Hi guys,

I'm in the middle of coding a events based site, and as you'd expect, the events site must show the date and time of each event.

However, I am thinking what is the best way to store the date and time for the event.

What type should I make the database row, and should I separate it into two rows, date and time. Or should I store it all together?

Also, is it possible to get some help on how I'd tie together, say from a dropdown list of dates and times into one, using PHP.


Love to hear what you have to say..


Regards,
Jamie

[eluser]Cristian Gilè[/eluser]
Hi JamieBarton,

according to me, the best way to store date and time in db, is to use int.
It's easy working with unix timestamp.

Cristian Gilè

[eluser]JamieBarton[/eluser]
Could you provide examples? I haven't coded in about 3 months, been very ill and trying to get my head around things again is showing difficulty haha

Thanks!!

[eluser]Cristian Gilè[/eluser]
Create a field in your table and set it as INT. In your model save the date and time using one of the date helper function like mysql_to_unix() or human_to_unix().

For example:

Code:

$year = $this->input->post('year');
$month = $this->input->post('month');
$day = $this->input->post('day');
$hour = $this->input->post('hour');
$min = $this->input->post('min');
$sec = $this->input->post('sec');

$int_date = mysql_to_unix($year.$month.$day.$hour.$min.$sec);

Cristian Gilè

[eluser]JamieBarton[/eluser]
[quote author="Cristian Gilè" date="1294689652"]Create a field in your table and set it as INT. In your model save the date and time using one of the date helper function like mysql_to_unix() or human_to_unix().

For example:

Code:

$year = $this->input->post('year');
$month = $this->input->post('month');
$day = $this->input->post('day');
$hour = $this->input->post('hour');
$min = $this->input->post('min');
$sec = $this->input->post('sec');

$int_date = mysql_to_unix($year.$month.$day.$hour.$min.$sec);

Cristian Gilè[/quote]

Excellent, looks good, I'll give that a go now... And then when I wanted to show it on a page..

Could I use any of PHP's date or time functions? Or would I be stuck with just printing out the year, month, without for example 'rd' in '3rd', or 'th' in '14th' etc..

Could you provide a final example for how I could echo out a result, found from for example, time_and_date in the DB..?



Regards,
Jamie!

[eluser]Cristian Gilè[/eluser]
mdate from codeigniter date helper.

Code:

$datestring = "Year: %Y Month: %m Day: %d - %h:%i %a";
$time = $this->you_model->get_timestamp();

echo mdate($datestring, $time); //or pass it to a view

Cristian Gilè

[eluser]Eric Barnes[/eluser]
Using time stamps is easy. Just use php date function:

Code:

echo date('F j, Y H:i:s', $date);

[eluser]carvingCode[/eluser]
Right. Use MySQL's 'datetime' field type. Then use PHP's date() function to display.

[eluser]AlexJ[/eluser]
INT timestamp takes less space then a datetime field

[eluser]Eric Barnes[/eluser]
[quote author="AlexJ" date="1294693475"]INT timestamp takes less space then a datetime field[/quote]

and in my opinion are much easier to work with.