Jump to content

Understanding mySQL dates and times.


TapeGun007

Recommended Posts

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.