corbyboy — 2011-04-22T06:40:09-04:00 — #1
I have an events table the date is separated into 3 columns.
CREATE TABLE IF NOT EXISTS `wc_events` (
`event_id` int(5) unsigned NOT NULL auto_increment,
`event_day` int(2) NOT NULL default '0',
`event_month` int(2) NOT NULL default '0',
`event_year` varchar(4) NOT NULL default '0',
`event_title` varchar(200) NOT NULL default '',
PRIMARY KEY (`event_id`)
I am trying to format the date using MySQL's DATE_FORMAT function. The trouble comes when I try to combine the three columns together to put into the function.
Something like this:
SELECT DATE_FORMAT(`event_year``event_month``event_day`,'%Y-%c-%e'') as format_t FROM wc_events
But this doesn't work. If anybody could help I would be very greatful.
starlion — 2011-04-22T06:48:28-04:00 — #2
out of curiousity, why are you storing the date in 3 columns to begin with?
corbyboy — 2011-04-22T08:17:48-04:00 — #3
It's part of a calendar. It makes it easier to display the calendar if they are split up. So when you browse January 2012, the script just looks for events where the month column is January and the year is 2012.
Additionally, events can recur every year, so if the day and month columns have data and the year is blank then we know it is an annual event.
If you could think of a better way to layout then data then I would not be against changing it.
system — 2011-04-22T08:55:12-04:00 — #4
Since you're not using actual date fields, DATE_FORMAT isn't appropriate.
PS - You should always store dates as dates, for proper (and simpler) chronological comparison of records... use DAY(event_date), MONTH(event_date) and YEAR(event_date) to return the integer values for scalar use.
starlion — 2011-04-22T09:03:50-04:00 — #5
I would just use a DATE field and use mySQL's inbuilt functions to slice off whatever parts of it you need (YEAR() MONTH() DAYOFMONTH() etc)...
But to stick to your format...
You're putting three field names back to back... and my guess is mySQL has no idea what you're trying to do. You probably need to CONCAT them together.
EDIT: transio woke up while i was sipping coffee and spending 15 minutes making a post!
corbyboy — 2011-04-22T09:05:34-04:00 — #6
Thanks for that. Is it possible to index the date field in such a way that you can use those functions?
system — 2011-04-22T09:05:43-04:00 — #7
Haha, pretty awesome that we said almost the identical thing on both parts, though!
system — 2011-04-22T09:06:29-04:00 — #8
Need more info... what are you looking to do with it?
corbyboy — 2011-04-22T09:23:19-04:00 — #9
What I mean is if I put the whole date in one column and use a query such as this:
SELECT event_id, event_title FROM wc_events WHERE MONTH(event_date)=1 AND YEAR(event_date)=2012
Can MySQL still make use of an index on the event_date field?
starlion — 2011-04-22T09:27:06-04:00 — #10
a DATE field is stored as YYYY-MM-DD. This particular format allows a string-comparison indexing of the field. (2012 will always come after 2011, because 2 comes after 1).
Functions dont destroy field structure; You can have WHERE YEAR(event_date) = 2012 ORDER BY event_date without issue.
r937 — 2011-04-22T13:57:40-04:00 — #11
actually, no it isn't
year-month-day sequence is mandatory for specifying a date string in query syntax (insert values, comparisons, etc.), but dates are stored in a completely different format
without any issue in correctness, yes, but definitely there is a deleterious issue in performance
the only way to allow an index on a column to be used, is to specify all conditions with the column on one side of a comparison operator
e.g. for january 2012 events, the only way to have the index on the date column to be used, for optimum efficiency, is...
WHERE datecol >= '2012-01-01'
AND datecol < '2012-02-01'
corbyboy — 2011-04-24T15:15:26-04:00 — #12
Thanks for confirming my suspicions about using indexes and functions. The solution I am going to go for is to store the whole date as well as each component separately.