[mysql] What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types

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 question is related to mysql sql datetime

The answer is


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


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


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.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?