What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types

Question

I need to store both time and date in the mysql. So I used of NOW() function for that. But I don't know what should I use for type column im phpmyadmin. It should be noted that NOW() returns both time and date like this:

2014-11-11 12:45:34

Here is a solution, I can use of a separator for separating date and time (2014-11-11 and 12:45:34) and then store them in the DATE type and TIME type individually. Or I can use of VARCHAR type for storing both of them in one column. But I think these ways are not standard. what is standard type for storing both date and time ?

Here is my query: (also I don't know why NOW() function does not works)

INSERT INTO table (timedate) VALUES (NOW())

[This article from the MySQL Documentation](https://dev.mysql.com/doc/refman/5.6/en/date-and-time-types.html) explains this well. — Matt Johnson-Pint, Aug 01 '15 at 21:25
@MattJohnson why not? I have a vendor that uses DateTime with 7 digit millisecond precision for financial transactions, which MySQL doesn't support. So, to meet our obligation of accurately storing the financial transaction with the accurate transaction datetime, we are forced to use a varchar. Without good cause or a business/regulatory need, sure. So, perhaps: "Without good cause such as a business/regulatory need, one should use datetime instead of varchar." — Robert Mauro, Feb 08 '19 at 20:18
@RobertMauro - There are all kinds of issues with doing that. Data size, indexing performance, query performance, ambiguity of various string formats, etc. Also, milliseconds only require 3 digits. MySQL supports up to microseconds (6 digits). The 7 digit formats usually (but not always) originate from Microsoft .NET or SQL Server, and it is nearly impossible to actually get an accurate timestamp from a system clock at that scale anyway. One might as well truncate or round to the nearest microsecond to store in MySQL. — Matt Johnson-Pint, Feb 08 '19 at 21:11
Also, such timestamps aren't actually needed in the financial sector. MiFID II is the only such regulation that applies (AFAIK), and under the highest level of its requirements, 1 microsecond is the smallest precision required. [Here is a good third-party overview of this](https://www.emissions-euets.com/time-stamping-and-business-clocks-synchronisation). Hope that helps you understand. Cheers! — Matt Johnson-Pint, Feb 08 '19 at 21:13
@MattJohnson sorry, no, NYS regulations, which bind insurance companies like ours, require that we can accurately and *exactly* replicate, recreate or replay all transactions. I do this part for a living, including working with the state. I have no choice but to exactly and accurately record the transactions as presented to us. I do know which database the 7 digit milliseconds are coming from as well. None of that is my question. I am forced by regulations to store 7 digit milliseconds, regardless of the accuracy of that data. I can see no method besides using a varchar. Is there another way? — Robert Mauro, Feb 08 '19 at 21:21
I think part of the confusion is you're looking at the requirements I need to follow for transaction *I* am assigning datetime stamps to. Alas, I wish it was that simple. I'm not making these datetime stamps. I'm simply mirroring the transaction on our end. :-( — Robert Mauro, Feb 08 '19 at 21:23
Ah, I see. Alas, since MySQL only supports 6 digits on `DATETIME` and `TIMESTAMP` values, then yes - you are correct. You may need to store that full timestamp somewhere else, such as in a `VARCHAR` field so you can exactly match on it. I'd recommend using ISO8601 extended format exclusively and consistently. It might still help to keep a secondary column of the native type to use for range queries, depending on your needs. Great discussion, BTW. ;) — Matt Johnson-Pint, Feb 08 '19 at 21:27
@MattJohnson Brilliant suggestion, I think I need more coffee since I didn't think about it. I'll index and utilize a datetime(6) field for queries, and accurately store the value presented to us in a varchar. Thanks! — Robert Mauro, Feb 08 '19 at 21:29

score 170 · Accepted Answer · edited May 27 '20 at 11:48

170

DATE: It is used for values with a date part but no time part. MySQL retrieves and displays DATE values in YYYY-MM-DD format. The supported range is 1000-01-01 to 9999-12-31.

DATETIME: It is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format. The supported range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

TIMESTAMP: It is also used for values that contain both date and time parts, and includes the time zone. TIMESTAMP has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.

TIME: Its values are in HH:MM:SS format (or HHH:MM:SS format for large hours values). TIME values may range from -838:59:59 to 838:59:59. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

edited May 27 '20 at 11:48

Théophile

1,032
10
16

answered Aug 01 '15 at 11:00

Saty

22,443
7
33
51

1

What is `UTC` in `TIMESTAMP` ? – Shafizadeh Aug 01 '15 at 11:33
1

@Sajad - UTC is [Coordinated Universal Time](https://en.wikipedia.org/wiki/Coordinated_Universal_Time). Be sure to recognize that `DATETIME` and `TIMESTAMP` differ in how they convert to and from local time to UTC. – Matt Johnson-Pint Aug 01 '15 at 21:27
@Shafizadeh any mysql function/query to convert timestamp to datetime ? – Sagar Panda Jul 06 '17 at 10:46
@SagarPanda https://stackoverflow.com/questions/5362874/how-to-convert-timestamp-to-datetime-in-mysql – Shafizadeh Jul 06 '17 at 10:54
33

this doesnt even really tell the difference between `datetime` and `timestamp` – oldboy Sep 30 '18 at 16:56
This means that datetime and timestamp are the same! – rotimi-best Feb 07 '19 at 10:25
@Anthony Yes, it does. The difference between `DATETIME` and `TIMESTAMP` is that the former does not include the timezone, whereas the latter does. This is obvious given the examples provided. – Ben Johnson May 17 '19 at 13:24
@BenJohnson the only difference exemplified above is the range of date and time... – oldboy May 17 '19 at 18:53
2

@Anthony No. The `DATETIME` example does not include a timezone, whereas the `TIMESTAMP` example includes `UTC`. – Ben Johnson May 17 '19 at 23:12
3

@BenJohnson fair enough. somebody ought to edit the answer so that its more understandable then – oldboy May 18 '19 at 00:36

Booboo · Answer 2 · 2020-02-25T22:16:07.747

53

I have a slightly different perspective on the difference between a DATETIME and a TIMESTAMP. A DATETIME stores a literal value of a date and time with no reference to any particular timezone. So, I can set a DATETIME column to a value such as '2019-01-16 12:15:00' to indicate precisely when my last birthday occurred. Was this Eastern Standard Time? Pacific Standard Time? Who knows? Where the current session time zone of the server comes into play occurs when you set a DATETIME column to some value such as NOW(). The value stored will be the current date and time using the current session time zone in effect. But once a DATETIME column has been set, it will display the same regardless of what the current session time zone is.

A TIMESTAMP column on the other hand takes the '2019-01-16 12:15:00' value you are setting into it and interprets it in the current session time zone to compute an internal representation relative to 1/1/1970 00:00:00 UTC. When the column is displayed, it will be converted back for display based on whatever the current session time zone is. It's a useful fiction to think of a TIMESTAMP as taking the value you are setting and converting it from the current session time zone to UTC for storing and then converting it back to the current session time zone for displaying.

If my server is in San Francisco but I am running an event in New York that starts on 9/1/1029 at 20:00, I would use a TIMESTAMP column for holding the start time, set the session time zone to 'America/New York' and set the start time to '2009-09-01 20:00:00'. If I want to know whether the event has occurred or not, regardless of the current session time zone setting I can compare the start time with NOW(). Of course, for displaying in a meaningful way to a perspective customer, I would need to set the correct session time zone. If I did not need to do time comparisons, then I would probably be better off just using a DATETIME column, which will display correctly (with an implied EST time zone) regardless of what the current session time zone is.

TIMESTAMP LIMITATION

The TIMESTAMP type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC and so it may not usable for your particular application. In that case you will have to use a DATETIME type. You will, of course, always have to be concerned that the current session time zone is set properly whenever you are using this type with date functions such as NOW().

edited Feb 25 '20 at 22:16

answered May 14 '19 at 21:17

Booboo

38,656
3
37
60

2

Excellent point. Note that `Timestamp` can only handle dates 1970 through 2038-01-19, whereas `DateTime` can handle years 1000 through 9999. Timestamp's primary use is for datetimes reasonably near the present. It will [probably be extended to 2116 before we get to year 2038](https://dev.mysql.com/worklog/task/?id=1872#targetText=Currently%20we%20support%20only%20dates,internal%20representation%20of%20TIMESTAMP%20type.) When using `DateTime`, I recommend having the input app convert to/from UTC. If that is done consistently, then DateTime can be considered UTC time, removing the ambiguity. – ToolmakerSteve Oct 28 '19 at 12:27
9

Keep in mind that 2038 problems don't wait for 2038 to happen, they can happen right now - for example, if I have a database of retirement funds which have a 20-year-target-horizon and I want to store that date, it will already wrap around and very bad things will happen. – Tim Feb 25 '20 at 21:03
3

[Real life example of problems already happening related to Y2K38](https://twitter.com/jxxf/status/1219009308438024200) – Tim Feb 25 '20 at 21:07
@Tim: Good point. Clearly if you need to work with dates that go to 2038 and beyond, then the only option is to use `DATETIME` types and consistent session time zone settings when initializing, displaying and comparing. I will add an update to my answer. – Booboo Feb 25 '20 at 21:59
@Booboo It's a great answer and I appreciate you posting it - just want to make clear to anybody stopping by the danger of 2038 and that it's happening now. I wish we had support for a field with time zone independence and post-2038 support! I guess one option is to use `datetime` and convert to UTC before inserting, but that's more error-prone on the client side – Tim Feb 25 '20 at 22:11
@Tim Given you're stuck with having to use a `DATETIME`, there is really nothing "holy" about first converting the value to UTC because you would probably want to convert it back to the session time zone, which has been set to "local" time, for displaying purpose. So you might as well just store it in the session time zone to begin with **as long as you ensure that you consistently set that time zone for all your sessions**. – Booboo Feb 25 '20 at 22:28
@Booboo It just adds extra steps that TIMESTAMP types don't require. For example, say I store all the events in my DATETIME field as UTC. But then a user wants to query events in their timezone, say from 9am to 5pm PDT, and see the results in the client's timezone. The client has to first convert user input from PDT to UTC, query the table, and then return the results cast back to PDT. Unless I'm missing something here? – Tim Feb 26 '20 at 21:05
@Tim *If* you have users across multiple time zones *and* you are designing your application to allow users to view dates and times in the time zones of their choice then certainly what you suggest makes sense. I am thinking of the YEAR 2038 problem where on the server side I am trying to determine on the server side whether a bond purchased on `2/26/2020 at 00:00:00 America/New_York` time (which let us assume is the time the seller uses for such purposes) and expires in 20 years has expired. No reason to convert to UTC. – Booboo Feb 26 '20 at 21:33
Excellent answer, MySQL should update their documentation with this haha. – Caio Dornelles Antunes Sep 29 '20 at 11:41

score 3 · Answer 3 · edited May 23 '17 at 12:02

3

Saty described the differences between them. For your practice, you can use datetime in order to keep the output of NOW().

For example:

CREATE TABLE Orders
(
  OrderId int NOT NULL,
  ProductName varchar(50) NOT NULL,
  OrderDate datetime NOT NULL DEFAULT NOW(),
  PRIMARY KEY (OrderId)
)

You can read more at w3schools.

edited May 23 '17 at 12:02

Community

1
1

answered Aug 01 '15 at 11:02

Hossein Mobasher

4,382
5
46
73

score 1 · Answer 4 · answered Dec 29 '22 at 09:48

In shorter explanation

DATE: The DATE stores a date value in the form YYYY-MM-DD (year-month-day). It does not store time.
TIME: The TIME stores a time value in the form HH:MM:SS (hours-minutes-seconds). It does not store the date.
DATETIME: The DATETIME stores a date and time value in the form YYYY-MM-DD HH:MM:SS. It stores both the date and time.
TIMESTAMP: The TIMESTAMP is similar to the DATETIME, but includes a timezone. (Example of values YYYY-MM-DD HH:MM:SS +HH:MM, YYYY-MM-DD HH:MM:SS -HH:MM. +HH:MM and -HH:MM indicate the time zone from UTC (Coordinated Universal Time).

What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types

4 Answers4

Linked