SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Feb 2008
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Formatting date in 3 different columns

    I have an events table the date is separated into 3 columns.

    Code MySQL:
    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`)
    ) ENGINE=MyISAM;

    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:

    Code MySQL:
    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.

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    out of curiousity, why are you storing the date in 3 columns to begin with?

  3. #3
    SitePoint Guru
    Join Date
    Feb 2008
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try CONCAT(event_year,'-',event_month,'-',event_day).

    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.

    Cheers.

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    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! :P

  6. #6
    SitePoint Guru
    Join Date
    Feb 2008
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    Try CONCAT(event_year,'-',event_month,'-',event_day).

    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.

    Cheers.
    Thanks for that. Is it possible to index the date field in such a way that you can use those functions?

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    EDIT: transio woke up while i was sipping coffee and spending 15 minutes making a post! :P
    Haha, pretty awesome that we said almost the identical thing on both parts, though!

  8. #8
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by corbyboy View Post
    Thanks for that. Is it possible to index the date field in such a way that you can use those functions?
    Need more info... what are you looking to do with it?

  9. #9
    SitePoint Guru
    Join Date
    Feb 2008
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I mean is if I put the whole date in one column and use a query such as this:

    Code MySQL:
    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?

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    a DATE field is stored as YYYY-MM-DD.
    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

    Quote Originally Posted by StarLion View Post
    Functions dont destroy field structure; You can have WHERE YEAR(event_date) = 2012 ORDER BY event_date without issue.
    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...
    Code:
    WHERE datecol >= '2012-01-01'
      AND datecol  < '2012-02-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Feb 2008
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •