Skip to content

Instantly share code, notes, and snippets.

@tayyebi

tayyebi/1.pdate.sql

Forked from joyofdata/gist:26e49e77285e35bc2439
Last active September 25, 2020 16:15
Show Gist options
  • Save tayyebi/8673657cb98aa114a01c4146ba83b3a8 to your computer and use it in GitHub Desktop.
Save tayyebi/8673657cb98aa114a01c4146ba83b3a8 to your computer and use it in GitHub Desktop.
Download ZIP
Setting Up a Time Dimension Table in MySQL
Raw
1.pdate.sql
-- Gregorian - Jalali Date Converter Functions for Mysql, v2.0.0
-- Copyright (C) 2019 Mohammad Saleh Souzanchi(saleh.souzanchi@gmail.com|https://github.com/zoghal), Mehran . M . Spitman
--
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program. If not, see <http://www.gnu.org/licenses/>.
-- Pathed by Amir Mirmoeini (Added read-only + deterministic flags to prevent errors in new and strict MySql or MariaDB environments)
-- ----------------------------
-- Function structure for `__mydiv`
-- ----------------------------
DROP FUNCTION IF EXISTS `__mydiv`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `__mydiv`(`a` int, `b` int) RETURNS bigint(20)
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2009-2019 Mohammad Saleh Souzanchi
# WebLog : www.saleh.soozanchi.ir
# Version V2.0.0
return FLOOR(a / b);
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `__mymod`
-- ----------------------------
DROP FUNCTION IF EXISTS `__mymod`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `__mymod`(`a` int, `b` int) RETURNS bigint(20)
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2011-2019 Mehran . M . Spitman
# WebLog :spitman.azdaa.com
# Version V2.0.0
return (a - b * FLOOR(a / b));
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `_gdmarray`
-- ----------------------------
DROP FUNCTION IF EXISTS `_gdmarray`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `_gdmarray`(`m` smallint) RETURNS smallint(2)
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2009-2019 Mohammad Saleh Souzanchi
# WebLog : www.saleh.soozanchi.ir
# Version V2.0.0
CASE m
WHEN 0 THEN RETURN 31;
WHEN 1 THEN RETURN 28;
WHEN 2 THEN RETURN 31;
WHEN 3 THEN RETURN 30;
WHEN 4 THEN RETURN 31;
WHEN 5 THEN RETURN 30;
WHEN 6 THEN RETURN 31;
WHEN 7 THEN RETURN 31;
WHEN 8 THEN RETURN 30;
WHEN 9 THEN RETURN 31;
WHEN 10 THEN RETURN 30;
WHEN 11 THEN RETURN 31;
END CASE;
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `_jdmarray`
-- ----------------------------
DROP FUNCTION IF EXISTS `_jdmarray`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `_jdmarray`(`m` smallint) RETURNS smallint(2)
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2009-2019 Mohammad Saleh Souzanchi
# WebLog : www.saleh.soozanchi.ir
# Version V2.0.0
CASE m
WHEN 0 THEN RETURN 31;
WHEN 1 THEN RETURN 31;
WHEN 2 THEN RETURN 31;
WHEN 3 THEN RETURN 31;
WHEN 4 THEN RETURN 31;
WHEN 5 THEN RETURN 31;
WHEN 6 THEN RETURN 30;
WHEN 7 THEN RETURN 30;
WHEN 8 THEN RETURN 30;
WHEN 9 THEN RETURN 30;
WHEN 10 THEN RETURN 30;
WHEN 11 THEN RETURN 29;
END CASE;
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `_jdmarray2`
-- ----------------------------
DROP FUNCTION IF EXISTS `_jdmarray2`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `_jdmarray2`(`m` smallint) RETURNS smallint(2)
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2011-2019 Mehran . M . Spitman
# WebLog :spitman.azdaa.com
# Version V2.0.0
CASE m
WHEN 1 THEN RETURN 31;
WHEN 2 THEN RETURN 31;
WHEN 3 THEN RETURN 31;
WHEN 4 THEN RETURN 31;
WHEN 5 THEN RETURN 31;
WHEN 6 THEN RETURN 31;
WHEN 7 THEN RETURN 30;
WHEN 8 THEN RETURN 30;
WHEN 9 THEN RETURN 30;
WHEN 10 THEN RETURN 30;
WHEN 11 THEN RETURN 30;
WHEN 12 THEN RETURN 29;
END CASE;
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `pdate`
-- ----------------------------
DROP FUNCTION IF EXISTS `pdate`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `pdate`(`gdate` datetime) RETURNS char(100) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2009-2019 Mohammad Saleh Souzanchi
# WebLog : www.saleh.soozanchi.ir
# Version V2.0.0
DECLARE
i,
gy, gm, gd,
g_day_no, j_day_no, j_np,jy INT DEFAULT 0; /* Can be unsigned int? */
DECLARE jm, jd INT(2) ZEROFILL DEFAULT 0;
DECLARE resout char(100);
DECLARE ttime CHAR(20);
SET gy = YEAR(gdate) - 1600;
SET gm = MONTH(gdate) - 1;
SET gd = DAY(gdate) - 1;
SET ttime = TIME(gdate);
SET g_day_no = ((365 * gy) + __mydiv(gy + 3, 4) - __mydiv(gy + 99, 100) + __mydiv (gy + 399, 400));
SET i = 0;
WHILE (i < gm) do
SET g_day_no = g_day_no + _gdmarray(i);
SET i = i + 1;
END WHILE;
IF gm > 1 and ((gy % 4 = 0 and gy % 100 <> 0)) or gy % 400 = 0 THEN
SET g_day_no = g_day_no + 1;
END IF;
SET g_day_no = g_day_no + gd;
SET j_day_no = g_day_no - 79;
SET j_np = j_day_no DIV 12053;
SET j_day_no = j_day_no % 12053;
SET jy = 979 + 33 * j_np + 4 * __mydiv(j_day_no, 1461);
SET j_day_no = j_day_no % 1461;
IF j_day_no >= 366 then
SET jy = jy + __mydiv(j_day_no - 1, 365);
SET j_day_no = (j_day_no - 1) % 365;
END IF;
SET i = 0;
WHILE (i < 11 and j_day_no >= _jdmarray(i)) do
SET j_day_no = j_day_no - _jdmarray(i);
SET i = i + 1;
END WHILE;
SET jm = i + 1;
SET jd = j_day_no + 1;
SET resout = CONCAT_WS ('-', jy, jm, jd);
IF (ttime <> '00:00:00') then
SET resout = CONCAT_WS(' ', resout, ttime);
END IF;
RETURN resout;
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `PMONTH`
-- ----------------------------
DROP FUNCTION IF EXISTS `PMONTH`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `PMONTH`(`gdate` datetime) RETURNS char(100) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2009-2019 Mohammad Saleh Souzanchi
# WebLog : www.saleh.soozanchi.ir
# Version V2.0.0
DECLARE
i,
gy, gm, gd,
g_day_no, j_day_no, j_np,
jy, jm, jd INT DEFAULT 0; /* Can be unsigned int? */
DECLARE resout char(100);
DECLARE ttime CHAR(20);
SET gy = YEAR(gdate) - 1600;
SET gm = MONTH(gdate) - 1;
SET gd = DAY(gdate) - 1;
SET ttime = TIME(gdate);
SET g_day_no = ((365 * gy) + __mydiv(gy + 3, 4) - __mydiv(gy + 99, 100) + __mydiv(gy + 399, 400));
SET i = 0;
WHILE (i < gm) do
SET g_day_no = g_day_no + _gdmarray(i);
SET i = i + 1;
END WHILE;
IF gm > 1 and ((gy % 4 = 0 and gy % 100 <> 0)) or gy % 400 = 0 THEN
SET g_day_no = g_day_no + 1;
END IF;
SET g_day_no = g_day_no + gd;
SET j_day_no = g_day_no - 79;
SET j_np = j_day_no DIV 12053;
set j_day_no = j_day_no % 12053;
SET jy = 979 + 33 * j_np + 4 * __mydiv(j_day_no, 1461);
SET j_day_no = j_day_no % 1461;
IF j_day_no >= 366 then
SET jy = jy + __mydiv(j_day_no - 1, 365);
SET j_day_no =(j_day_no - 1) % 365;
END IF;
SET i = 0;
WHILE (i < 11 and j_day_no >= _jdmarray(i)) do
SET j_day_no = j_day_no - _jdmarray(i);
SET i = i + 1;
END WHILE;
SET jm = i + 1;
SET jd = j_day_no + 1;
RETURN jm;
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `pmonthname`
-- ----------------------------
DROP FUNCTION IF EXISTS `pmonthname`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `pmonthname`(`gdate` datetime) RETURNS varchar(100) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2009-2019 Mohammad Saleh Souzanchi
# WebLog : www.saleh.soozanchi.ir
# Version V2.0.0
CASE PMONTH(gdate)
WHEN 1 THEN RETURN 'Farvardin';
WHEN 2 THEN RETURN 'Ordibehesht';
WHEN 3 THEN RETURN 'Khordad';
WHEN 4 THEN RETURN 'Tir';
WHEN 5 THEN RETURN 'Mordad';
WHEN 6 THEN RETURN 'Shahrivar';
WHEN 7 THEN RETURN 'Mehr';
WHEN 8 THEN RETURN 'Aban';
WHEN 9 THEN RETURN 'Azar';
WHEN 10 THEN RETURN 'Dey';
WHEN 11 THEN RETURN 'Bahman';
WHEN 12 THEN RETURN 'Esfand';
END CASE;
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `pyear`
-- ----------------------------
DROP FUNCTION IF EXISTS `pyear`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `pyear`(`gdate` datetime) RETURNS char(100) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2009-2019 Mohammad Saleh Souzanchi
# WebLog : www.saleh.soozanchi.ir
# Version V2.0.0
DECLARE
i,
gy, gm, gd,
g_day_no, j_day_no, j_np,
jy, jm, jd INT DEFAULT 0; /* Can be unsigned int? */
DECLARE resout char(100);
DECLARE ttime CHAR(20);
SET gy = YEAR(gdate) - 1600;
SET gm = MONTH(gdate) - 1;
SET gd = DAY(gdate) - 1;
SET ttime = TIME(gdate);
SET g_day_no = ((365 * gy) + __mydiv(gy + 3, 4) - __mydiv(gy + 99, 100) + __mydiv(gy + 399, 400));
SET i = 0;
WHILE (i < gm) do
SET g_day_no = g_day_no + _gdmarray(i);
SET i = i + 1;
END WHILE;
IF gm > 1 and ((gy % 4 = 0 and gy % 100 <> 0)) or gy % 400 = 0 THEN
SET g_day_no = g_day_no + 1;
END IF;
SET g_day_no = g_day_no + gd;
SET j_day_no = g_day_no - 79;
SET j_np = j_day_no DIV 12053;
set j_day_no = j_day_no % 12053;
SET jy = 979 + 33 * j_np + 4 * __mydiv(j_day_no, 1461);
SET j_day_no = j_day_no % 1461;
IF j_day_no >= 366 then
SET jy = jy + __mydiv(j_day_no - 1, 365);
SET j_day_no = (j_day_no - 1) % 365;
END IF;
SET i = 0;
WHILE (i < 11 and j_day_no >= _jdmarray(i)) do
SET j_day_no = j_day_no - _jdmarray(i);
SET i = i + 1;
END WHILE;
SET jm = i + 1;
SET jd = j_day_no + 1;
RETURN jy;
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `pday`
-- ----------------------------
DROP FUNCTION IF EXISTS `pday`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `pday`(`gdate` datetime) RETURNS char(100) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2011-2019 Mohammad Saleh Souzanchi, Mehran . M . Spitman
# WebLog : www.saleh.soozanchi.ir, spitman.azdaa.com
# Version V2.0.0
DECLARE
i,
gy, gm, gd,
g_day_no, j_day_no, j_np,
jy, jm, jd INT DEFAULT 0; /* Can be unsigned int? */
DECLARE resout char(100);
DECLARE ttime CHAR(20);
SET gy = YEAR(gdate) - 1600;
SET gm = MONTH(gdate) - 1;
SET gd = DAY(gdate) - 1;
SET ttime = TIME(gdate);
SET g_day_no = ((365 * gy) + __mydiv(gy + 3, 4) - __mydiv(gy + 99 , 100) + __mydiv(gy + 399, 400));
SET i = 0;
WHILE (i < gm) do
SET g_day_no = g_day_no + _gdmarray(i);
SET i = i + 1;
END WHILE;
IF gm > 1 and ((gy % 4 = 0 and gy % 100 <> 0)) or gy % 400 = 0 THEN
SET g_day_no = g_day_no + 1;
END IF;
SET g_day_no = g_day_no + gd;
SET j_day_no = g_day_no - 79;
SET j_np = j_day_no DIV 12053;
SET j_day_no = j_day_no % 12053;
SET jy = 979 + 33 * j_np + 4 * __mydiv(j_day_no, 1461);
SET j_day_no = j_day_no % 1461;
IF j_day_no >= 366 then
SET jy = jy + __mydiv(j_day_no - 1, 365);
SET j_day_no = (j_day_no-1) % 365;
END IF;
SET i = 0;
WHILE (i < 11 and j_day_no >= _jdmarray(i)) do
SET j_day_no = j_day_no - _jdmarray(i);
SET i = i + 1;
END WHILE;
SET jm = i + 1;
SET jd = j_day_no + 1;
RETURN jd;
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `_gdmarray2`
-- ----------------------------
DROP FUNCTION IF EXISTS `_gdmarray2`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `_gdmarray2`(`m` smallint, `k` SMALLINT) RETURNS smallint(2)
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2011-2019 Mehran . M . Spitman
# WebLog :spitman.azdaa.com
# Version V2.0.0
CASE m
WHEN 0 THEN RETURN 31;
WHEN 1 THEN RETURN 28+k;
WHEN 2 THEN RETURN 31;
WHEN 3 THEN RETURN 30;
WHEN 4 THEN RETURN 31;
WHEN 5 THEN RETURN 30;
WHEN 6 THEN RETURN 31;
WHEN 7 THEN RETURN 31;
WHEN 8 THEN RETURN 30;
WHEN 9 THEN RETURN 31;
WHEN 10 THEN RETURN 30;
WHEN 11 THEN RETURN 31;
END CASE;
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `gdate`
-- ----------------------------
DROP FUNCTION IF EXISTS `gdate`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `gdate`(`jy` smallint, `jm` smallint, `jd` smallint) RETURNS datetime
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2011-2019 Mehran . M . Spitman
# WebLog :spitman.azdaa.com
# Version V2.0.0
DECLARE
i, j, e, k, mo,
gy, gm, gd,
g_day_no, j_day_no, bkab, jmm, mday, g_day_mo, bkab1, j1
INT DEFAULT 0; /* Can be unsigned int? */
DECLARE resout char(100);
DECLARE fdate datetime;
SET bkab = __mymod(jy,33);
IF (bkab = 1 or bkab= 5 or bkab = 9 or bkab = 13 or bkab = 17 or bkab = 22 or bkab = 26 or bkab = 30) THEN
SET j=1;
end IF;
SET bkab1 = __mymod(jy+1,33);
IF (bkab1 = 1 or bkab1= 5 or bkab1 = 9 or bkab1 = 13 or bkab1 = 17 or bkab1 = 22 or bkab1 = 26 or bkab1 = 30) THEN
SET j1=1;
end IF;
CASE jm
WHEN 1 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 2 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 3 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 4 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 5 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 6 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 7 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 8 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 9 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 10 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 11 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 12 THEN IF jd > _jdmarray2(jm)+j or jd <= 0 THEN SET e=1; end IF;
END CASE;
IF jm > 12 or jm <= 0 THEN SET e=1; end IF;
IF jy <= 0 THEN SET e=1; end IF;
IF e>0 THEN
RETURN 0;
end IF;
IF (jm>=11) or (jm=10 and jd>=11 and j=0) or (jm=10 and jd>11 and j=1) THEN
SET i=1;
end IF;
SET gy = jy + 621 + i;
IF (__mymod(gy,4)=0) THEN
SET k=1;
end IF;
IF (__mymod(gy,100)=0) and (__mymod(gy,400)<>0) THEN
SET k=0;
END IF;
SET jmm=jm-1;
WHILE (jmm > 0) do
SET mday=mday+_jdmarray2(jmm);
SET jmm=jmm-1;
end WHILE;
SET j_day_no=(jy-1)*365+(__mydiv(jy,4))+mday+jd;
SET g_day_no=j_day_no+226899;
SET g_day_no=g_day_no-(__mydiv(gy-1,4));
SET g_day_mo=__mymod(g_day_no,365);
IF (k=1 and j=1) THEN
IF (g_day_mo=0) THEN
RETURN CONCAT_WS('-',gy,'12','30');
END IF;
IF (g_day_mo=1) THEN
RETURN CONCAT_WS('-',gy,'12','31');
END IF;
END IF;
IF (g_day_mo=0) THEN
RETURN CONCAT_WS('-',gy,'12','31');
END IF;
SET mo=0;
SET gm=gm+1;
while g_day_mo>_gdmarray2(mo,k) do
SET g_day_mo=g_day_mo-_gdmarray2(mo,k);
SET mo=mo+1;
SET gm=gm+1;
end WHILE;
SET gd=g_day_mo;
RETURN CONCAT_WS('-',gy,gm,gd);
END;;
DELIMITER ;
-- ----------------------------
-- Function structure for `gdatestr`
-- ----------------------------
DROP FUNCTION IF EXISTS `gdatestr`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `gdatestr`(`jdat` char(10)) RETURNS datetime
READS SQL DATA
DETERMINISTIC
BEGIN
# Copyright (C) 2011-2019 Mehran . M . Spitman
# WebLog spitman.azdaa.com
# Version V2.0.0
DECLARE
i, j, e, k, mo,
gy, gm, gd,
g_day_no, j_day_no, bkab, jmm, mday, g_day_mo, jd, jy, jm,bkab1,j1
INT DEFAULT 0; /* ### Can't be unsigned int! ### */
DECLARE resout char(100);
DECLARE jdd, jyd, jmd, jt varchar(100);
DECLARE fdate datetime;
SET jdd = SUBSTRING_INDEX(jdat, '/', -1);
SET jt = SUBSTRING_INDEX(jdat, '/', 2);
SET jyd = SUBSTRING_INDEX(jt, '/', 1);
SET jmd = SUBSTRING_INDEX(jt, '/', -1);
SET jd = CAST(jdd as SIGNED);
SET jy = CAST(jyd as SIGNED);
SET jm = CAST(jmd as SIGNED);
SET bkab = __mymod(jy,33);
IF (bkab = 1 or bkab= 5 or bkab = 9 or bkab = 13 or bkab = 17 or bkab = 22 or bkab = 26 or bkab = 30) THEN
SET j=1;
end IF;
SET bkab1 = __mymod(jy+1,33);
IF (bkab1 = 1 or bkab1= 5 or bkab1 = 9 or bkab1 = 13 or bkab1 = 17 or bkab1 = 22 or bkab1 = 26 or bkab1 = 30) THEN
SET j1=1;
end IF;
CASE jm
WHEN 1 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 2 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 3 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 4 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 5 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 6 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 7 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 8 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 9 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 10 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 11 THEN IF jd > _jdmarray2(jm) or jd <= 0 THEN SET e=1; end IF;
WHEN 12 THEN IF jd > _jdmarray2(jm)+j or jd <= 0 THEN SET e=1; end IF;
END CASE;
IF jm > 12 or jm <= 0 THEN SET e=1; end IF;
IF jy <= 0 THEN SET e=1; end IF;
IF e>0 THEN
RETURN 0;
end IF;
IF (jm>=11) or (jm=10 and jd>=11 and j=0) or (jm=10 and jd>11 and j=1) THEN
SET i=1;
end IF;
SET gy = jy + 621 + i;
IF (__mymod(gy,4)=0) THEN
SET k=1;
end IF;
IF (__mymod(gy,100)=0) and (__mymod(gy,400)<>0) THEN
SET k=0;
END IF;
SET jmm=jm-1;
WHILE (jmm > 0) do
SET mday=mday+_jdmarray2(jmm);
SET jmm=jmm-1;
end WHILE;
SET j_day_no=(jy-1)*365+(__mydiv(jy,4))+mday+jd;
SET g_day_no=j_day_no+226899;
SET g_day_no=g_day_no-(__mydiv(gy-1,4));
SET g_day_mo=__mymod(g_day_no,365);
IF (k=1 and j=1) THEN
IF (g_day_mo=0) THEN
RETURN CONCAT_WS('-',gy,'12','30');
END IF;
IF (g_day_mo=1) THEN
RETURN CONCAT_WS('-',gy,'12','31');
END IF;
END IF;
IF (g_day_mo=0) THEN
RETURN CONCAT_WS('-',gy,'12','31');
END IF;
SET mo=0;
SET gm=gm+1;
while g_day_mo>_gdmarray2(mo,k) do
SET g_day_mo=g_day_mo-_gdmarray2(mo,k);
SET mo=mo+1;
SET gm=gm+1;
end WHILE;
SET gd=g_day_mo;
RETURN CONCAT_WS('-',gy,gm,gd);
END;;
DELIMITER ;
Raw
2.dimension.my.sql
-- Note: Jalali Functions forked from [https://github.com/zoghal/Presian-Date-for-MySQL]
DROP TABLE IF EXISTS `dw_T`;
CREATE TABLE `dw_T` (
`n` char(30)
);
INSERT INTO `dw_T`(n) SELECT @row := @row + 1 as `row` FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t6,
(SELECT @row:=0) t7;
-- time span
SET @d0 = "2019-01-01 00:00:00";
SET @d1 = "2019-01-10 00:00:00";
SET @date = @d0;
-- get mysql timezone
select @timezone := timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));
-- set up the time dimension table
DROP TABLE IF EXISTS dw_time_dimension;
CREATE TABLE `dw_time_dimension` (
`DateTime` datetime DEFAULT NULL,
`Id` char(10) NOT NULL,
`TimeZone` char(9) NOT NULL,
`Year` smallint DEFAULT NULL,
`Month` smallint DEFAULT NULL,
`Day` smallint DEFAULT NULL,
`YearWeek` smallint DEFAULT NULL,
`Week` smallint DEFAULT NULL,
`Quarter` smallint DEFAULT NULL,
`WeekDay` smallint DEFAULT NULL,
`MonthName` char(10) DEFAULT NULL,
`WeekDayName` char(10) DEFAULT NULL,
`Hour` tinyint default NULL,
`JalaliDateTime` char(19) NOT NULL,
`JalaliYear` smallint NOT NULL,
`JalaliMonth` smallint NULL,
`JalaliDay` smallint NULL,
`JalaliMonthName` char(11) NOT NULL,
PRIMARY KEY (`Id`)
);
-- populate the table with dates
INSERT INTO dw_time_dimension
SELECT @date := date_add(@date, interval 1 hour) as 'DateTime',
-- integer ID that allows immediate understanding
date_format(@date, "%Y%m%d%H") as 'Id',
@timezone as 'TimeZone',
year(@date) as 'Year',
month(@date) as 'Month',
day(@date) as 'Day',
date_format(@date, "%x") as 'YearWeek',
week(@date) as 'Week',
quarter(@date) as 'Quarter',
weekday(@date)+1 as 'WeekDay',
monthname(@date) as 'MonthName',
dayname(@date) as 'WeekDayName',
hour(@date) as 'Hour',
pdate(@date) as 'JalaliDateTime',
pyear(@date) as 'JalaliYear',
pmonth(@date) as 'JalaliMonth',
pday(@date) as 'JalaliDay',
pmonthname(@date) as 'JalaliMonthName'
FROM dw_T
WHERE date_add(@date, interval 1 hour) <= @d1
ORDER BY `DateTime`;
-- drop temp table
DROP TABLE `dw_T`;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment