Jump to content

booking appointments


jimfog
 Share

Recommended Posts

I am making a web app where the user will be able to use a calendar to store appointments in the database.

The question is what type of column that must be that will store the appointment time.

 

Without having thoroughly researched the issue my understanding is that it must be of the date/time format.

But I am not sure if this is the right choice-could STRING be also a solution here?

Link to comment
Share on other sites

Yes, but if I want to retrieve the time from the database so as to present it to

the user(displaying the appointments booked in other words) will the unix timestamp be the appropriate choice also for this occasion?

Link to comment
Share on other sites

Are you asking if you can format a Unix timestamp however you want to show it? Yes, you can. Otherwise, they wouldn't be very useful.

The only problem with a unix timestamp is that they are not "readable" when you want to open the db application(phpmyadmin for example) and see the data

there directly instead of waiting PHP to print in the browser.

Link to comment
Share on other sites

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

 

Use that function to list the contents. I don't design my database around making it easy for me to read the database though, I design it around making it easy for the computer to use it. Representing any given second as an integer is pretty easy for the computer to use.

Link to comment
Share on other sites

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

 

Use that function to list the contents. I don't design my database around making it easy for me to read the database though, I design it around making it easy for the computer to use it. Representing any given second as an integer is pretty easy for the computer to use.

I agree with you but from time to time I want to be able to read the contents of the database,in the end though your argument about "...easy for the computer to use" makes a point. I assume that "translates to" making calculations faster.

Link to comment
Share on other sites

  • 1 month later...

I want to make a clarification here...something I had to do earlier.

The thing I want to store in the db is NOT the moment the user actually makes the booking,but the booking time itself,I am trying to find a suitable column format for this.

For example,the user booked an appointment for 13:00.How am I gong to store this "13:00" at the db,as a VARCHAR?

Link to comment
Share on other sites

I would still recommend saving any date / time as a unix timestamp, if it's now, the past, or in the future. If you really want a readable version, I would create another column and use the DATE type. I wouldn't use it in my application, I would always use the timestamp. But at least you can have a readable version.

Edited by thescientist
Link to comment
Share on other sites

  • 3 weeks later...

ok, I want to add something more in our conversation.Specifically regarding unix conversion.

The date/time comes to the server(via AJAX) as a string.More precisely this is what comes from the user's PC:

 

start: "2014-02-11T19:30:00.000Z"

end: "2014-02-11T20:00:00.000Z"

The above comes as a JSON object. With start/end marking the duration of the event.
Obviously some string functions must be used to separate what needs separation.
But having seen the above-do you still think that the date/time(as it is above) must be stored as a unix timestamp.
Link to comment
Share on other sites

Why do you think that how you store dates and times in a database has any relationship to the format in which you receive the data? What about what you described makes you think that the data type in the database needs to change? Is it because it's part of a JSON object? Is it because it's a string? I don't understand why you're confused. All dates and times start out as strings. You convert them to an actual representation of a date and time, for example PHP's DateTime object, or Javascript's Date object, and then you can use it in the code. When you save it in the database you convert it to the data type that you're storing in the database. It doesn't matter if it came from JSON, or XML, or a web service, or some API, or a system call, or whatever else. Dates and times are all the same, correct? Have you ever seen a date and time that somehow needs to be treated differently than any other date and time? Does PHP or Javascript have multiple kinds of date/time objects for handling different kinds of dates, or are all date/times the same? Is there any date/time I'm not aware of that does not consist of a year, month, day, hours, minutes, seconds, and timezone? Do you have any reason why you think those dates should not be stored as a Unix timestamp? Why is a Unix timestamp the wrong way to store that date?

Link to comment
Share on other sites

I prefer to do what's easiest for my applications interests. I don't want to dictate form or presentation at that low of a level, that should be up to the presentation layer or any other consumer of the potential data (like an API). A timestamp is the most portable way to depict a moment in time that can be easily formatted calculated by any language that can get the data in the first place.

Edited by thescientist
Link to comment
Share on other sites

  • 4 weeks later...

Yes a unix timestamp is the correct choice.But there is a question I want to make.

As I said,this is what comes from the client:

 

"2014-02-11T19:30:00.000Z"

 

The above passes to a variable(array) and then I use strtotime to convert the above to unix timestamp:

$unixdate=strtotime($content['start']);

$content is the array.$unixdate is what will be going to the db after all-but an adjustment must be made.

Related to the timezone-for example:

strtotime('2014-03-10T11:00:00.-0200')

I must shift the timestamp by 2 hours.

Given the fact that what arrives in the PHP script is a variable that is coverted to a unix timestamp how I could apply the timezone adjustment as shown in the example above?

Link to comment
Share on other sites

  • 4 weeks later...

My problem now has to do with the syntax-selecting specifically the unix timestamp from the table column and converting it.

I tried this(need to stress here that the column where unix timestamp is stored is called startDate)...here is what I tried:

SELECT FROM_UNIXTIME(select startDate from appointments); 

And here is the error message I get:

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select startDate from appointments)' at line 1

Ηow can I select the unix timestamp from the column and the same type use FROM_UNIXTIME?

Link to comment
Share on other sites

Functions like FROM_UNIXTIME do not take a query as a parameter, they take a single column or value.

Yes...but how am I going to "target" a specific cell from a table with FROM_UNIXTIME?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...