CODEWITHSUNDEEP
mysql
Bluemagica
Bluemagica

Reputation: 5158

Can someone please explain to me the logic of this mysql select query values?

Concat(Date_format(Date_add(Date_format(Str_to_date(
                                               products_options_values,
                                               '%m-%d-%Y'
                                                      ),
                                                      '%Y-%m-%d'),
                                             INTERVAL(2-Dayofweek(Date_format(
                                             Str_to_date(
                                             products_options_values, '%m-%d-%Y'
                                             )
                                             , '%Y-%m-%d')))day), '%b%d'), '-',
       Date_format(Date_add(Date_format(Str_to_date(products_options_values,
                                        '%m-%d-%Y'
       )
       ,
       '%Y-%m-%d'
       ),
       INTERVAL( 8 - Dayofweek( Date_format(Str_to_date(products_options_values,
       '%m-%d-%Y'
       ), '%Y-%m-%d')) )day), '%b%d'))
       AS week_dates,
       Week(Date_format(Str_to_date(products_options_values, '%m-%d-%Y'),
            '%Y-%m-%d'))
                   AS weekdays

I need to implement the above mysql date calculation logic in jquery. This is part of a mysql select statement to get dates. But I failed to understand what is happening exactly with this date calculations, can someone please explain this?

Upvotes: 0

Views: 89

Answers (1)

eggyal
eggyal

Reputation: 125855

The key to understanding code like this is to work from the inside outwards. A quick glance through your code quickly reveals that the innermost function calls are the same:

DATE_FORMAT(STR_TO_DATE(products_options_values, '%m-%d-%Y'), '%Y-%m-%d')

The innermost of those two function calls, STR_TO_DATE, takes the string in products_options_values and converts it to a MySQL DATE value by parsing it according to the format given in the second argument (i.e. US-style month-day-year). The outermost function call, DATE_FORMAT, takes that resulting DATE value and formats it in the form given by its second argument (i.e. year-month-day).

That is, together those calls convert a date represented in US-style form into a more international format. Rewriting the rest of your query using international_date in place of those calls:

Concat(
  Date_format(
    Date_add(
      international_date,
      INTERVAL(
        2 - Dayofweek(international_date)
     ) day
   ),
   '%b%d'
  ),
  '-',
  Date_format(
    Date_add(
      international_date,
      INTERVAL(
        8 - Dayofweek(international_date)
      ) day
    ),
    '%b%d'
  )
) AS week_dates,
Week(international_date) AS weekdays

The DAYOFWEEK function returns the given date's day of the week as an integer from 1 to 7 (where 1 is Sunday and 7 is Saturday). Therefore, adding 2 - DAYOFWEEK(international_date) days will give the Monday of the same week; and adding 8 - DAYOFWEEK(international_date) will give the Sunday of the following week. That is precisely what the DATE_ADD functions are doing.

Those dates (Monday of the same week and Sunday of the following week) are then formatted in a specific form (three letter month and two-digit day, e.g. Jan14 or May18) and concatenated with CONCAT using a separating hyphen. The result is given the alias week_dates.

There is also another column in the resultset which is given the alias weekdays; this holds the value of the WEEK function as applied to international_date (that is, its week of the year).

Upvotes: 1

Related Questions