Should i use datetime or timestamp

Knowledge Base Contact Login Search. MariaDB starting with For more information, see Internal Format. Comments loading With strict mode enabled, invalid dates generate an error.

See Section 5. Dates containing 2-digit year values are ambiguous because the century is unknown. MySQL interprets 2-digit year values using these rules:. Year values in the range become See also Section Numeric Type Attributes. Out-of-Range and Overflow Handling. Date and Time Data Type Syntax. Fractional Seconds in Time Values. Conversion Between Date and Time Types. The Geometry Class Hierarchy. Not even YEAR will help you out. It is a number. Not only is this a number, it is an incrementing number, and without gaps.

Ever ascending. The value which follows is The implication of this internal representation is that conversions must be made back and forth. Can you provide with a convincing argument? The only argument I can find is hardly convincing. Did you ever need to type in a password with your phone?

Say, for your Credit Card company, or your Bank? In the case of your Credit Card company, there is no excuse. Read this page on the docs to find out that a DATETIME can be represented in many forms, and can be automatically deduced from a string in many formats. Active Oldest Votes. Improve this answer. Saty Saty Anthony No.

BenJohnson fair enough. Show 5 more comments. Booboo Booboo Excellent point. Note that Timestamp can only handle dates through , whereas DateTime can handle years through Timestamp's primary use is for datetimes reasonably near the present.

Keep in mind that problems don't wait for to happen, they can happen right now - for example, if I have a database of retirement funds which have a year-target-horizon and I want to store that date, it will already wrap around and very bad things will happen. Real life example of problems already happening related to Y2K38 — Tim.

Tim: Good point. Clearly if you need to work with dates that go to 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 It's a great answer and I appreciate you posting it - just want to make clear to anybody stopping by the danger of and that it's happening now.

I wish we had support for a field with time zone independence and post 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.

Community Bot 1 1 1 silver badge. Hossein Mobasher Hossein Mobasher 4, 5 5 gold badges 40 40 silver badges 67 67 bronze badges. Add a comment.