根据儒略日计算实际时间 c#+mysql

儒略日（Julian day）是指由公元前4713年1月1日，协调世界时中午12时开始所经过的天数，多为天文学家采用，用以作为天文学的单一历法，把不同历法的年表统一起来。
求出给定年(I)，月(J)，日(K)的儒略日:
儒略日= K - 32075 + 1461 * (I + 4800 + (J-14)/12)/4+367*(J-2-(J-14)/12*12)/12-3*((I+4900+(J-14)/12)/100)/4
网上只找到了如何将时间转成如略日，由于工作需要，要求将如略日转回实际时间。

特此写了c#和mysql的转换方法。

c# 

/// <summary>
        /// 根据儒略日计算实际时间（王月 11月23日）
        /// </summary>
        /// <param name="num"></param>
        /// <param name="StartTime"></param>
        /// <returns></returns>
        public static DateTime GetNormal(double num, DateTime StartTime)
        {
            double num1 = Math.Floor(num);
            double year = num1 / 365.25;
            double ThisYear = Math.Floor(year);
            double month = (year - ThisYear) * 365.25f / 30.4375f;
            double ThisMonth = Math.Floor(month); 
            double day = (month - ThisMonth) * 30.4375f;
            double ThisDay = Math.Floor(day);
            int ThisHour = 0;
            double hour = (day - ThisDay) * 24;
            int h = 0;
            if (hour > 20) h = 1;
            int f = Convert.ToInt32((num - num1) * 100);
            switch (f)
            {
                case 0:
                    ThisHour = 0; break;
                case 25:
                    ThisHour = 6; break;
                case 50:
                    ThisHour = 12; break;
                case 75:
                    ThisHour = 18; break;
                default: break;
            }
            StartTime = StartTime.AddDays(ThisDay + 1 + h);
            StartTime = StartTime.AddMonths(Convert.ToInt32(ThisMonth));
            StartTime = StartTime.AddYears(Convert.ToInt32(ThisYear));
            DateTime dt = new DateTime(StartTime.Year, StartTime.Month, StartTime.Day, ThisHour, 0, 0);
            return dt;
        }

 

调用： GetNormal(16025.5, new DateTime(1858, 11, 17));

 

mysql：

DELIMITER $$
DROP FUNCTION IF EXISTS test.hello $$
CREATE FUNCTION test.hello (num varchar(50)) RETURNS datetime
begin
declare outvalue datetime;
declare ThisYear int(10);
declare ThisMonth int(10);
declare ThisDay int(10);
declare ThisHour int(10);
declare hours double;
declare h int(10);
declare f int(10);
declare thisdayh int(10);

set ThisYear = floor(floor(num)/ 365.25);
set ThisMonth = floor((floor(num)/ 365.25- ThisYear) * 365.25 / 30.4375);
set ThisDay = floor(((floor(num)/ 365.25- ThisYear) * 365.25 / 30.4375-ThisMonth)* 30.4375);
set hours = (((floor(num)/ 365.25- ThisYear) * 365.25 / 30.4375-ThisMonth)* 30.4375 - ThisDay)*24;
set h = if(hours>20,1,0);
set f = (num - floor(num))*100;

set ThisHour = if(f=0,0, ThisHour);
set ThisHour = if(f=25,6, ThisHour);
set ThisHour = if(f=50,12, ThisHour);
set ThisHour = if(f=75,18, ThisHour);
set thisdayh = ThisDay + 1 + h;
set outvalue = CAST('1858-11-17' as datetime);
set outvalue = DATE_ADD('1858-11-17 00:00:00',INTERVAL thisdayh DAY);
set outvalue = DATE_ADD(outvalue, Interval ThisMonth month);
set outvalue = DATE_ADD(outvalue, Interval ThisYear YEAR);
set outvalue = DATE_ADD(outvalue, Interval ThisHour HOUR);

RETURN outvalue;
END $$
DELIMITER ;

 

调用：update test.tab set time2 = hello(time1);///time1是如略日的字段，time2是添加的北京时间的字段