CODEWITHSUNDEEP
phpmysql
MAX POWER
MAX POWER

Reputation: 5448

MySQL - Single DATETIME or Separate DATE and TIME Columns?

In my application I'm developing a functionality for creating "reminders".

A reminder has a date and a time. In my application, I have a form to create / edit reminders - this has two separate fields to input this information:

<input type="text" name="date"></input> <!-- datepicker plugin -->

<input type="text" name="time"></input> <!-- timepicker plugin -->

Now as a rule I have always used a DATETIME column whenever I have needed to store date/time, however this is the first time I'm having to store a user inputted date/time.

I figured it would be best to have seperate DATE and TIME columns, because it would be easier to insert / retrieve the data to / from my application. For example I won't have to combine the values from the two input fields to create a single value to insert in to the database. And likewise I won't have to split a single value in to two values to populate the form fields in edit mode.

But on the other hand won't it be easier to query the table if I used one column? What do you think?

Upvotes: 3

Views: 5398

Answers (6)

Oleg Vorontsov
Oleg Vorontsov

Reputation: 1

An example comes to my mind as to when have date and time split:

You could want to have DATE a part of the unique index, so that a user is only allowed to add 1 record to some table per date, but still you want to know the TIME he added it, so you keep DATE and TIME separate.

Upvotes: 0

victor diaz
victor diaz

Reputation: 9

what if you are developing a reservation application and at one end you need to know on what date and at what time to schedule an appointment for a user, and at the other end, you need to match the user to a doctors schedule. You save the doctors schedule in a database and you need to know (amoung other things) when the doctor is available (on what days), and at what times. Let us forget about the on what days for a moment, and focus on the time shedule first... You need to develop a programmable schedule so that if you know that the doctor works 6 months in a particular calendar year. (Jan - Jun), He or she may work (9-5 M,W,Fr), and (10-3 T,Th). Sat and Sunday the doctor is off. So you develop a table to hold the Daily time schedule with 2 columns to hold the daily starttime and daily end time for each day of the week. 14 columns in total and a primary and possibly secondary key. So now its time for some date arithmetic (This is where it gets hairy:-|...

You can say i your query: (mySQL)

Select such and such...

where form.theapptdatetime between doctorschedule_startime_tuesday and doctorschedule_endime_tuesday

and this will do a match to see if your datetime is within the date range of your doctorschedulestartime and endtime... but what if all you need is the time??

will the date arithmetic still work if the time value is stored as a datetime???

In other words if I have 01:00:00 as my doctorschedule_startime, is this a legitimate date value for my arithmetic to work, or will a date portion be forced upon me.

Perhaps I should store the time as a varchar, and convert it to a suitable datetime value and perform the arithmetic in the code instead of the query????

Upvotes: 0

Thijsma
Thijsma

Reputation: 1

Doesn't it depends on the system you're creating. If you want to store dates beyond 2038 I would store the datetime and time separate.

Upvotes: 0

Abid Hussain
Abid Hussain

Reputation: 7762

Please see it

Adding a Timepicker to jQuery UI Datepicker

http://trentrichardson.com/examples/timepicker/

convert your date time according to your mysql format and store it

$mydate = strtotime($_POST['date']);
$myfinaldate = date("d-m-y", $mydate);

$mytime = strtotime($_POST['time']);
$myfinaltime = date("H:i:s", $mytime);

Upvotes: 1

user1329212
user1329212

Reputation:

Seperating columns is unlogical. You can use timestamp as datatype and you can use mktime function to parse date and time easily.

Upvotes: 0

Paul McNett
Paul McNett

Reputation: 847

You should build bottom-up (database at the bottom). Don't think about the application, just the database. Now, what makes sense at the database level. DateTime.

So you need to write extra code at the application level.

Upvotes: 4

Related Questions