Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How store date in MySQL database?

Tags:

mysql

I have date in dd/mm/yyyy format. How can I store it in a database, if I want to do some operations on it afterwards?

For example, I must find out the rows, where date > something. What type I must set for the date field?

like image 677
Simon Avatar asked May 19 '10 22:05

Simon


People also ask

How does MySQL store date?

MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type 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.

How do I insert date in YYYY-MM-DD format in MySQL?

You can use str_to_date to convert a date string to MySQL's internal date format for inserting.

How do databases store dates?

Databases will convert any datetime into a UTC epoch to store internally. However, some databases may enable storing timezone information. If that's the case, it's recommended to convert all dates to UTC before storing.


1 Answers

To store dates or times in MySQL use date, datetime or timestamp. I'd recommend the first two for most purposes.

To tell MySQL how to parse your date format use the STR_TO_DATE function. Here's an example:

CREATE TABLE table1 (`Date` Date);
INSERT INTO table1 (`Date`) VALUES (STR_TO_DATE('01/05/2010', '%m/%d/%Y'));
SELECT * FROM table1;

Date
2010-01-05

To format the results back into the original form look at the DATE_FORMAT function. Note that you only need to format it if you want to display it as a string using something other than the default format.

like image 66
Mark Byers Avatar answered Sep 21 '22 21:09

Mark Byers