StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If I understand your question correctly, my idea is to count the number of seconds between the first day of the next year and the first day of the current year:</p> <pre><code>SELECT UNIX_TIMESTAMP(MAKEDATE(1994+1,1)) - UNIX_TIMESTAMP(MAKEDATE(1994,1)) </code></pre> <p>Then multiply this number for the fractional part of the given date:</p> <pre><code>SELECT (UNIX_TIMESTAMP(MAKEDATE(1994+1,1)) - UNIX_TIMESTAMP(MAKEDATE(1994,1))) * 0.232424 </code></pre> <p>This gives the number of seconds since the beginning of the year. And you can then add this number of seconds to the first day of the year:</p> <pre><code>SELECT MAKEDATE(1994,1) + INTERVAL (UNIX_TIMESTAMP(MAKEDATE(1994+1,1)) - UNIX_TIMESTAMP(MAKEDATE(1994,1))) * 0.232424 SECOND; </code></pre> <p>This is a generic query:</p> <pre><code>SET @yourdate = 1994.232424; SELECT MAKEDATE(TRUNCATE(@yourdate, 0),1) + INTERVAL (UNIX_TIMESTAMP(MAKEDATE(TRUNCATE(@yourdate, 0)+1,1)) - UNIX_TIMESTAMP(MAKEDATE(TRUNCATE(@yourdate, 0),1))) * (@yourdate-TRUNCATE(@yourdate, 0)) SECOND; </code></pre> <p><strong>EDIT</strong></p> <p>I wanted to use UNIX_TIMESTAMP because I didn't want to calculate if a year was a leap year or not, I just wanted MySql to calculate it automatically. But there are other options.</p> <p>The number of seconds in a year is 31536000 + 86400 more if the year is a leap year.</p> <p>Another idea is to use <code>MAKEDATE(year, 60)</code>, to see if the returned date is in March or in February. <code>MAKEDATE(TRUNCATE(@yourdate, 0),60)=2</code> will be evaluated to TRUE (=1) on leap years, and to FALSE (=0) otherwise:</p> <pre><code>SELECT MAKEDATE(TRUNCATE(@yourdate, 0),1) + INTERVAL (31536000 + (MAKEDATE(TRUNCATE(@yourdate, 0),60)=2)*(86400)) * (@yourdate-TRUNCATE(@yourdate, 0)) SECOND; </code></pre> <p>But of course you could use your formula:</p> <pre><code>(31536000 + (((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0))*86400) </code></pre> <p>to calculate the number of seconds of the year, and then multiply this result for the fractional part.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload