Understanding mySQL dates and times.

[TapeGun007]

I've read quite a bit about Date, DateTime, TimeStamp, and Time that after awhile, it's gets a bit confusing because everyone seems to have an opinion about which is best to do what in code examples.

All I really want to do is store an appointment say, 05/23/22 at 10:00 AM  and store that into the database, and be able to recall it later in that same format.  Then I want to store when that appointment is going to end (which will always be the same day).

So what would be the best route to take, so I can narrow it down.  Like it is better to store the appointment date under the Date field and a separate value for the time the appointment started, or would it make more sense to store the appointment start time with a DateTime stamp instead?  The part I'm not understanding from reading, is which one do you use for what scenario.  I mean, I think Date is pretty simple.  The Time field is weird, but I can see how you could use it to track the variance in time rather than a set calendar-like time like I'm doing.

I believe I'm correct in saying that TimeStamp is really for tracking changes like... to a record and perhaps you have a timestamp for every time the record changes?

I *think* I should use the DateTime for the start of the appointment and maybe just TimeStamp for the end of the appointment?  Any advice or perhaps a link to better reading material would be greatly appreciated.

[requinix]

Sounds like you're overthinking this. If you want a date and time then you should use a data type that supports a date and time. Storing them separately as a DATE in one column and a TIME in the other makes it more complicated than it needs to be.

Then the question is whether you use DATETIME or TIMESTAMP. They are mostly the same, but the key differences are that (a) DATETIMEs have a much wider range of dates they can support and (b) MySQL will reinterpret TIMESTAMPs according to server/client timezone configuration.
Longer story short, DATETIME is nice for arbitrary date/time values that your application will manage (like your appointment example) while TIMESTAMP is nice for record-keeping purposes (such as when a table row was written or updated).

But it doesn't really matter that much. It's perfectly okay to use DATETIME for everything, so if you're still unsure then just use that and move on to the next challenge.

[Barand]

And if you are worried about storage, a DATETIME (without fractional seconds) only requires 5 bytes.

[gizmola]

I've written about using the Timestamp type.  It would be appropriate for your application, and a timestamp only requires 4 bytes.  You want to turn off the basic timestamp features when you define the column, but that is covered in my article:  https://www.gizmola.com/blog/archives/93-Too-much-information-about-the-MySQL-TIMESTAMP.html

As Barand stated, a Datetime is not much worse at 5 bytes as of MySQL version 5.6.4 when some storage mechanics were changed.  Previous to that a Datetime used 8 bytes, but now it's much more efficient, so long as you don't need fractional seconds.

For a scheduling app either one is fine and they are basically interchangeable as far as PHP is concerned, and the SQL statements and functions you can use them with.

[Barand]

Since DATETIME and TIMESTAMP now behave similarly, you can now have a column to auto-record the time inserted and a separate on to record update times EG

CREATE TABLE `a_test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `start` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finish` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The differences are

• Being 1 byte smaller (4 bytes) TIMESTAMP has a reduced date range
• TIMESTAMP is stored as UTC time and converted back to current timezone on retrieval

[TapeGun007]

Very good gentlemen and thank you.  I think I've just read so much that after awhile it seemed like I was getting conflicting opinions.  This helps to clarify greatly!