How best store year, month, and day in a MySQL database so that it would be easily retrieved by year, by year-month, by year-month-day combinations?
+3
A:
Unless a time will ever be involved, use the DATE data type. You can use functions from there to select portions of the date.
LittleBobbyTables
2010-10-17 02:16:12
A:
That is a vague and subjective question but please read this article:
http://www.richardlord.net/blog/dates-in-php-and-mysql
It will guide you in the right direction!
Trufa
2010-10-17 02:16:41
+2
A:
Store as date and use built in functions:day(), month() or year() to return the combination you wish.
james_bond
2010-10-17 02:17:30
+1
A:
What's wrong with DATE? As long as you need Y, Y-M, or Y-M-D searches, they should be indexable. The problem with DATE would be if you want all December records across several years, for instance.
Larry Lustig
2010-10-17 02:21:05
+4
A:
Let's say you have a table tbl with a column d of type DATE.
All records in 1997:
SELECT * FROM tbl WHERE YEAR(d) = 1997
SELECT * FROM tbl WHERE d BETWEEN '1997-01-01' AND '1997-12-31'
All records in March of 1997:
SELECT * FROM tbl WHERE YEAR(d) = 1997 AND MONTH(d) = 3
SELECT * FROM tbl WHERE d BETWEEN '1997-03-01' AND '1997-03-31'
All records on March 10, 1997:
SELECT * FROM tbl WHERE d = '1997-03-10'
benzado
2010-10-17 02:28:43
I'm the farthest thing of a MySQL specialist that there is, but I notice that the MySQL docs suggest explicitly casting string literals to dates when querying (_WHERE d BETWEEN CAST('1997-03-01' AS DATE) AND CAST('1997-03-31')_). I assume that otherwise, the date column values are converted to strings to allow wildcard comparisons, making it impossible to use an index on the column.
Larry Lustig
2010-10-17 12:52:20
Larry, I've never heard that before, but it makes a lot of sense. Thanks!
benzado
2010-10-18 06:08:48