booking appointments

[jimfog]

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?

[justsomeguy]

I store both date and time in int columns as a Unix timestamp.

[thescientist]

definitely unix timestamp

[jimfog]

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?

[justsomeguy]

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.

[jimfog]

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.

[justsomeguy]

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.

[jimfog]

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.

[jimfog]

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?

[thescientist]

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 January 25, 2014 by thescientist

[jimfog]

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.

[justsomeguy]

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?

[thescientist]

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 February 14, 2014 by thescientist

[jimfog]

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?

[thescientist]

first result seems promising

https://www.google.com/search?q=apply+timezone+change+to+unix+timestamp+php&oq=apply+timezone+change+to+unix+timestamp+php&aqs=chrome..69i57.270j0j4&sourceid=chrome&espv=210&es_sm=119&ie=UTF-8

[jimfog]

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?

[justsomeguy]

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

[jimfog]

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?

[justsomeguy]

There are a ton of examples online that you could look up.SELECT FROM_UNIXTIME(column) FROM table WHERE ...