12/30/2023 0 Comments Mysql date diff![]() ![]() (SELECT period1) + (SELECT period2) + (SELECT period3) AS monthsĪlthough it's an old topic it shows on top in google and I don't see newer questions related to Mysql to calculate the difference in months. IF(MONTH((SELECT startdate)) = MONTH((SELECT enddate)), (SELECT days), DAY((SELECT enddate))) / DAY(LAST_DAY((SELECT enddate))) AS period3, TIMESTAMPDIFF(MONTH, LAST_DAY((SELECT startdate)) + INTERVAL 1 DAY, LAST_DAY((SELECT enddate))) AS period2, IF(MONTH((SELECT startdate)) = MONTH((SELECT enddate)), 0, (TIMESTAMPDIFF(DAY, (SELECT startdate), LAST_DAY((SELECT startdate)) + INTERVAL 1 DAY)) / DAY(LAST_DAY((SELECT startdate)))) AS period1, TIMESTAMPDIFF(DAY, (SELECT startdate), (SELECT enddate)) AS days, I ended up with the following query: SELECT A day in February divided by the number of days in February is not equal to a day in May divided by the number of days in May. Although Zane Bien's solution is in the right direction, his second and third examples give inaccurate results. I needed month-difference with precision. DELIMITER $$ĬREATE FUNCTION datedifference(date1 DATE, date2 DATE) RETURNS DATE When you get into serious applications that use Date/Time information like for example the financial markets, Julian dates are de-facto.Įxecute this code and it will create a function datedeifference which will give you the difference in date format yyyy-mm-dd. Using pointers I have it down to about 900 Clks (This is also a conversion from a STRING to an INTEGER of course) Neither are good to look at, so I use the Timestamps when I need a column that I will not be doing much calculation with, but I want an at-a-glance indication.Ĭonverting to Julian and back can be done very quickly in a good language. UNIXDateTime values are good to work with when making Date/Time calculations Julian Dates are very nice to work with when making date calculations If this is stored in a DWORD (unsigned 4 Byte Integer) then dates all the way up to 2106 can be stored as seconds since epoc, ĭWORD max val = 4,294,967,295 - A DWORD can hold 136 years of Seconds These can be combined to form a UnixDateTime. ![]() I would reccomend using Julian Date and Seconds from midnight for all date/time values. Apart from the fact that they occupy 36 Bytes, they are not at all convenient to work with. Where startdate and enddate are your date parameters, whether it be from two date columns in a table or as input parameters from a script:Įxamples: With startdate = '' AND enddate = '': ![]() TIMESTAMPDIFF(MONTH, startdate, enddate) + 1 TIMESTAMPDIFF(MONTH, startdate, enddate) + It's a little more complicated if you want to introduce decimal precision in the number of months elapsed, but here is how you can do it: SELECT This solution automatically compensates for the varying amount of days in each month (28,30,31) as well as taking into account leap years - you don't have to worry about any of that stuff. It basically gets the number of months elapsed from the first date in the parameter list. You can specify MONTH as the unit in the first parameter: SELECT TIMESTAMPDIFF(MONTH, '', '') ![]() What this allows you to do is pass in two TIMESTAMP or DATETIME values (or even DATE as MySQL will auto-convert) as well as the unit of time you want to base your difference on. Have a look at the TIMESTAMPDIFF() function in MySQL. I'm surprised this hasn't been mentioned yet: Month-difference between any given two dates: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |