CODEWITHSUNDEEP
mysql
Aditya Mukherji
Aditya Mukherji

Reputation: 9256

MySql Leap Years

I have a MySql table called reviews which stores a lot of product reviews, accumulated over many years, each annotated with a date timestamp.
I wanted to draw a bar-chart showing me the number of reviews I have accumulated on each day of the year, hoping to find the important dates for my system.

I used this query

select dayofyear(date),count(*) from reviews group by dayofyear(date);

However, after noticing that this is returning 366 rows instead of 365, I have realized that I cannot use this for making this chart, because the day index is getting offset by 1 every leap year, which is skewing my data.

For example, Christmas is showing up as day #359 on most years, but its #360 on leap years.

What is the simplest way to resolve this skew?

On a sidenote, is there any software package that can accept an SQL query and return the results directly as a bar-chart (when a bar-chart makes sense)

Upvotes: 1

Views: 6877

Answers (6)

Tom Hopkins
Tom Hopkins

Reputation: 21

Just check the day of year value for any date AFTER Feb 28 in the current year. The DAYOFYEAR() function will return 60 for Feb 29 during a leap year. It will return NULL for Feb 29 during non-leap years.

SELECT 'IS LEAP YEAR' WHERE DAYOFYEAR(CONCAT(YEAR(NOW()),'-02-29')) = 60;

OR

SELECT 'IS LEAP YEAR' WHERE DAYOFYEAR(CONCAT(YEAR(NOW()),'-03-01')) = 61;

Upvotes: 0

黃奕捷
黃奕捷

Reputation: 5

/*
isLeapYear function:
 parameter: 1. `year`: indicates the year. 
 returned value: return 1 iff year is a leap year. return 0 otherwise.
*/
DELIMITER //
   CREATE FUNCTION isLeapYear(year INT)
      RETURNS INT
      IF (year  % 400 = 0) THEN
        RETURN 1;
      END IF;
      IF (year  % 100 = 0) THEN
        RETURN 0;
      END IF;
      IF (year  % 4 = 0) THEN
        RETURN 1;
      END IF;
      RETURN 0;
 END; // DELIMITER ;

Upvotes: -1

黃奕捷
黃奕捷

Reputation: 5

  1. think about the feature of leap year.

  2. In a leap year, there are 29 days in Febuary.

To check a date (for example mydate field) is a leap year. Use the following condition.

DAY(LAST_DAY(DATEMAKE(YEAR(mydate),2))) = 29

The full MySQL statement

SELECT 
    IF(DAY(LAST_DAY(DATEMAKE(YEAR(mydate),2))) = 29,
        IF(DAYOFYEAR(mydate) > DAYOFYEAR("2008-02-28"), DAYOFYEAR(mydate) - 1, DAYOFYEAR(mydate)),
        DAYOFYEAR(mydate))
FROM mytbl

  1. For the how many days does one month has, see stackoverflow

  2. For how to make a new date in MySQL, see w3school

Upvotes: 0

Vinit Dabhi
Vinit Dabhi

Reputation: 1089

DELIMITER //
   CREATE FUNCTION leap_year (y INT)
      RETURNS INT
  DECLARE income INT;
      IF (y%4 = 0) THEN
        SET income = TRUE;
      ELSE
        SET income = FALSE;
      END IF;
      RETURN income;
  END; // DELIMITER ;

The above written code is to find a leap year using function in MySQL Workbench, which I use mostly(workbench). Once you write this code and execute this, you can see the function is create in left side with leap_year in schema.

You need to call this function to see the output of this function. For calling this function.

SELECT leap_year(2023);

Once you hit above line and see the output will show you "0", which means 2023 is not a leap year.

I hope you understand this.

Upvotes: -3

Emil H
Emil H

Reputation: 40240

I haven't tested this, but you could do something like the following:

SELECT 
        # check if the year is a leap year:
        IF((YEAR(mydate) % 4 = 0 AND YEAR(mydate) % 100 != 0) OR YEAR(mydate) % 400 = 0,
            # if so, check if the date is before or after february 29th. if that is the case, we subtract one from the value
            IF(DAYOFYEAR(mydate) > DAYOFYEAR("2008-02-28"), DAYOFYEAR(mydate) - 1, DAYOFYEAR(mydate)),
            # if the year isn't a leap year, just return the regular dayofyear() value
            DAYOFYEAR(mydate))
    FROM mytbl

This will merge the data for the 28th and 29th for leap years, but give the same offsets for the days during leap years and non-leap years for all other days. A more desirable behaviour could be to simply ignore the data from february 29th, which could be accomplished using additional conditions. You could also assign it a special index, such as 400, which won't offset all other days.

A better way might be to group by month and day of month:

select month(date) m, day(date) d, count(*) from reviews group by m, d;

This avoids the problem all together, but might be more complicated for you to process in your application logic. Still, I'd argue that it's a lot better way to do it.

Upvotes: 4

John
John

Reputation: 16007

Group them by day of month, and rinse and repeat for each month?

Upvotes: 1

Related Questions