Date VS Datetime

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

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).

Should I use the datetime or timestamp data type in MySQL?

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Date vs DateTime

No there isn't. DateTime represents some point in time that is composed of a date and a time. However, you can retrieve the date part via the Date property (which is another DateTime with the time set to 00:00:00).

And you can retrieve individual date properties via Day, Month and Year.

UPDATE: In .NET 6 the types DateOnly and TimeOnly are introduced that represent just a date or just a time.

DATE vs. DATETIME casting of invalid dates in SQL SERVER 2008 R2

It is worth mentioning that DATE and DATETIME are completely different datatypes. DATE is not simply DATETIME with the time removed. For example, CAST('17520910' AS DATE) works, while the similar conversion to DATETIME does not. For you history buffs, there was no such day in England or her colonies, the calendar skipped from September 2nd to September 14. Unlike DATETIME, DATE goes back to the year 1 without considering calendar system.

Another important difference is the lack of implicit type conversion to add a number of days directly to a date. If D is datetime, D+3 is the date three days hence. If D is DATE, then D+3 produces an error.

I am assuming that since new code for implicit conversion was created from scratch for DATE that Microsoft simply made it a tad more fastidious.

Date conversion and culture: Difference between DATE and DATETIME

The ISO-8601 for DATETIME (the older type) is somehow "broken" or "adapted" (depending on whether you look at it as a bug or a feature) - you need to use YYYYMMDD (without any dashes) to make it work irrespective of the language settings.

For DATE or the DATETIME2(n) datatypes, this has been fixed and the "proper" ISO-8601 format YYYY-MM-DD will always be interpreted correctly.

-- OK because of "adapted" ISO-8601
SET LANGUAGE GERMAN;
DECLARE @dt DATETIME='20170113'; 

SELECT @dt;

SELECT CAST('20170113' AS DATETIME);
SELECT CONVERT(DATETIME, '20170113'); 

-- OK because of DATETIME2(n)
SET LANGUAGE GERMAN;
DECLARE @dt2 DATETIME2(0) = '2017-01-13'; 

SELECT @dt2;

SELECT CAST('2017-01-13' AS DATETIME2(0));
SELECT CONVERT(DATETIME2(0), '2017-01-13'); 

It's a quirk of the DATETIME type (and not the only one....) - just register it, know about it - and move on (meaning: don't use DATETIME anymore - use DATE or DATETIME2(n) instead - much nicer to work with!) :-)

Datetime vs Date and Time Mysql

I tend to think there are basically no advantages to storing the date and time in separate fields. MySQL offers very convenient functions for extracting the date and time parts of a datetime value.

Okay. There can be some efficiency reasons. In MySQL, you can put separate indexes on the fields. So, if you want to search for particular times, for instance, then a query that counts by hours of the day (for instance) can use an index on the time field. An index on a datetime field would not be used in this case. A separate date field might make it easier to write a query that will use the date index, but, strictly speaking, a datetime should also work.

The one time where I've seen dates and times stored separately is in a trading system. In this case, the trade has a valuation date. The valuation time is something like "NY Open" or "London Close" -- this is not a real time value. It is a description of the time of day used for valuation.

BigQuery - Datetime vs Timestamp

In most cases you will want to use the timestamp data type. It refers to an absolute point in time. BigQuery interprets any timezone information and represents the time internally as a UTC timestamp.

Very rarely would you use a datetime data type, which is a date and a time but no time zone. The example I like to give is that you'd use a datetime to represent pi day, 2017, since it occurs at 2017-03-14 15:09:26.535898 in each time zone separately.

What is faster for only date search DATETIME or DATE format

I have not benchmarked it, but based on what I know about the MySQL code I would expect the difference to be marginal under normal use. DATE uses 3 bytes for storage, while DATETIME uses 8 bytes. This difference will be significant in some abnormal cases, e.g. if the DATE/DATETIME column is the only one or one of the very few in a table that has millions of rows, or if you have a lot of such columns. Under normal usage the overhead of DATETIME will drown relative to other database operations. This would be the case in particular if you have good keys and are able to avoid table scans with thousands of unnecessary DATE/DATETIME comparisons. So I would say do not worry about it and just use DATETIME.

---

Related Topics