Make Gadgets Work

After updating from Ubuntu 14.04, the php and Apache stopped being friends and one of the WordPress site I maintain went all white and admin page was just showing php code. This is apparently because of a known issue in 16.04 with upgrade to php7 as shown on the ubuntu forum here Using the guidance from this link and with some more of duckduckgo search later, I managed to resolve the problem thus: #1. Install aptitude if it is not already installed using sudo apt-get install aptitude #2. Removed php7 and unwanted php using sudo aptitude purge `dpkg -l | grep php| awk '{print $2}' |tr "\n" " "` #3. Added old repo using sudo add-apt-repository ppa:ondrej/php #4. Updated repo sudo apt-get update #5. Installed php5.6 sudo apt-get install php5.6 sudo apt-get install php5.6-mbstring php5.6-mcrypt php5.6-mysql php5.6-xml php5.6-curl php5.6-gd php5.6-zip #6. Checked php version sudo php -v #7. Enabled mod_php sudo a2enmod php5 Ignored error message #8. Opened php5.6 conf sudo nano /etc/apache2/mods-enabled/php5.6.conf #9. Commented following lines <IfModule mod_userdir.c> <Directory /home/*/public_html> php_admin_flag engine Off </Directory> </IfModule> #10. Restarted the server sudo service apache2 restart

Fix for PHP Issues after upgrade to Ubuntu 16.04.1 (Xenial)

After updating from Ubuntu 14.04, the php and Apache stopped being friends and one of the WordPress site I maintain went all white and admin page was just showing php code. This is apparently because of a known issue in 16.04 with upgrade to php7 as shown on
While the guidance on Ghost website is very clear, I did get issues that required steps in troubleshooting. Something to do with lodash and npm version 2 stuff (node_modules/knex requires lodash@'^3.7.0') that I read on one of the forums specifically the comment from ErisDS on 13/06. Anyway, reading this I deleted node_modules followed by npm install and it worked. All commands in order as I did are listed below. If my previous posts were used to create the blog nothing here will require sudo or root privileges. As before all this was done on Fedora 24 Linux OS and following commands will need to be changed where it mentions yoursite and username. If the path is different then obviously entire path needs to be replaced. #Copy the entire site as backup. It will be a verbose copy an all access rights will be preserved. cp -avr /var/www/html/yoursite /home/<username>/ #Now in the site directory create a directory ghostlatest mkdir /var/www/html/yoursite/ghostlatest #change directory to ghostlatest cd /var/www/html/yoursite/ghostlatest #now download the latest ghost zip file curl -LOk https://ghost.org/zip/ghost-latest.zip #unzip the downloaded file unzip ghost-latest.zip #stop your Ghost instance (assuming Ghost is the alias #created as per my previous post else replace with #whatever alias was used with pm2). pm2 stop Ghost #change directory and delete old folders and files cd /var/www/html/yoursite rm -rf core rm -rf index.js rm -rf *.md rm -rf *.json rm -rf /var/www/html/yoursite/content/themes/casper #remove node_modules because anyway the lodash issue will hit later on. rm -rf node_modules #copy from ghost latest to site directory new folders cp -avr /var/www/html/yoursite/ghostlatest/core /var/www/html/yoursite cp -avr /var/www/html/yoursite/ghostlatest/index.js /var/www/html/yoursite cp -avr /var/www/html/yoursite/ghostlatest/*.md /var/www/html/yoursite cp -avr /var/www/html/yoursite/ghostlatest/*.json /var/www/html/yoursite #optional if you haven't made customisation to default theme. cp -avr /var/www/html/yoursite/ghostlatest/content/themes/casper /var/www/html/yoursite/content/themes #Install Latest Version npm cache clean npm update npm install --production #Start to update dependencies npm start --production #Once above command is complete, stop the server and restart using pm2 Ctrl+C pm2 start Ghost

Update Ghost on Fedora

While the guidance on Ghost website is very clear, I did get issues that required steps in troubleshooting. Something to do with lodash and npm version 2 stuff (node_modules/knex requires lodash@'^3.7.0') that I read on one of the forums specifically the comment from ErisDS
Steps: Install Oracle Java Doanload Tomcat Check MD5 Create a TOMCAT Group and User then grant access Create Service for Tomcat Alternative Start and Stop Change Port Add Tomcat Users Test Configure Nginx Reverse Proxy for Tomcat Install Oracle Java #install jdk wget --no-cookies --no-check-certificate --header "Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F; oraclelicense=accept-securebackup-cookie" "http://download.oracle.com/otn-pub/java/jdk/8u102-b14/jdk-8u102-linux-i586.rpm" #install jre wget --no-cookies --no-check-certificate --header "Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F; oraclelicense=accept-securebackup-cookie" "http://download.oracle.com/otn-pub/java/jdk/8u102-b14/jre-8u102-linux-i586.rpm" #enable firefox plugin alternatives --install /usr/lib/mozilla/plugins/libjavaplugin.so libjavaplugin.so /usr/java/jdk1.8.0_102/jre/lib/i386/libnpjp2.so 20000 URL for JDK and JRE is best obtained directly from oracle website - http://www.oracle.com/technetwork/java/javase/downloads/index.html #Download Tomcat ```language-bash line-numbers su mkdir /opt/tomcat/ && cd /opt/tomcat wget http://mirror.ox.ac.uk/sites/rsync.apache.org/tomcat/tomcat-8/v8.5.4/bin/apache-tomcat-8.5.4.zip wget https://www.apache.org/dist/tomcat/tomcat-8/v8.5.4/bin/apache-tomcat-8.5.4.zip.md5 ``` Check MD5 cat apache-tomcat-8.5.4.zip.md5 md5sum apache-tomcat-8.5.4.zip unzip apache-tomcat-8.5.4.zip Create a TOMCAT Group and User then grant access groupadd tomcat useradd -M -s /bin/nologin -g tomcat -d /opt/tomcat tomcat cd /opt/tomcat chgrp -R tomcat conf chmod g+rwx conf chmod g+r conf/* chown -R tomcat bin/ webapps/ work/ temp/ logs/ Create Service for Tomcat nano /etc/systemd/system/tomcat.service # Systemd unit file for tomcat [Unit] Description=Apache Tomcat Web Application Container After=syslog.target network.target [Service] Type=forking ExecStart=/opt/tomcat/apache-tomcat-8.5.4/bin/startup.sh ExecStop=/opt/tomcat/apache-tomcat-8.5.4/bin/shutdown.sh User=tomcat Group=tomcat [Install] WantedBy=multi-user.target systemctl start tomcat.service systemctl enable tomcat.service Alternative start and stop cd apache-tomcat-8.5.4/bin chmod 700 /opt/tomcat/apache-tomcat-8.5.4/bin/*.sh ln -s /opt/tomcat/apache-tomcat-8.5.4/bin/startup.sh /usr/bin/tomcatup ln -s /opt/tomcat/apache-tomcat-8.5.4/bin/shutdown.sh /usr/bin/tomcatdown tomcatup tomcatdown Change port nano /opt/tomcat/apache-tomcat-8.5.4/conf/server.xml Around line 69 is the connector tag where the port=8080 is specified. For this example lets change it to 8081. After change the connector tag in server.xml will look as below: <Connector port="8081" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" /> Add tomcat Users #open tomcat-users.xml and add new users before </tomcat-user> tag nano /opt/tomcat/apache-tomcat-8.5.4/conf/tomcat-users.xml sample user: <role rolename="admin-gui"/> <user username="admin" password="some admin password" roles="admin-gui"/> <role rolename="manager-gui"/> <user username="jhondoe" password="some password" roles="manager-gui"/> Test touch /opt/tomcat/apache-tomcat-8.5.4/webapps/ROOT/testankit.jsp nano /opt/tomcat/apache-tomcat-8.5.4/webapps/ROOT/testankit.jsp #restart tomcat systemctl restart tomcat.service Open the browser and enter http://localhost:8080 (or whatever port you have configured Tomcat on.) Configure Nginx Reverse Proxy for Tomcat Configure the dynamic DNS. Steps will be as per my previous post. For purpose of this step I will be assuming you created a DDNS named tomcat.yoursite.com Update /etc/hosts to include tomcat.yoursite.com sudo nano /etc/hosts #Make an entry in your hosts 127.0.0.1 localhost.localdomain localhost your.seafile.com your.blog.com tomcat.yoursite.com Now create nginx conf file using sudo nano /etc/nginx/conf.d/tomcat.confas shown below upstream tomcat { server 127.0.0.1:8081; } server { listen 80; server_name tomcat.yoursite.com; access_log /var/log/nginx/tomcat.access.log; error_log /var/log/nginx/tomcat.error.log; proxy_buffers 16 64k; proxy_buffer_size 128k; location / { proxy_pass http://tomcat; proxy_next_upstream error timeout invalid_header http_500 http_502 http_503 http_504; proxy_redirect off; proxy_set_header Host $host; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header X-Forwarded-Proto https; } } Finally reload and restart services sudo systemctl daemon-reload sudo systemctl start nginx.service sudo systemctl start tomcat.service

Tomcat 8.5.4 on Fedora behind Nginx

Steps: Install Oracle Java Doanload Tomcat Check MD5 Create a TOMCAT Group and User then grant access Create Service for Tomcat Alternative Start and Stop Change Port Add Tomcat Users Test Configure Nginx Reverse Proxy for Tomcat Install Oracle Java #install jdk wget --no-cookies --no-check-certificate --header "Cookie: gpw_e24=
Steps: Configure Namecheap Set-up DDCLIENT Test DDCLIENT Set up DDCLIENT to run at start-up Known Issue with DDCLIENT Configure Namecheap Follow the Namecheap guide here NOTE: For a subdomain "oxygen.copper.com", just replace @ with "oxygen" Set-up DDCLIENT #Install DDCLIENT on Fedora sudo dnf install ddclient #Edit the configuration file to update IP on your Dynamic DNS host sudo nano /etc/ddclient/ddclient.conf Press Ctrl+W and type the name of host for your Dynamic DNS. Mine is with Namecheap and I needed to configure for the subdomain hence following config reflects how to do it for Namecheap. For other hosts, you will need to refer their documentation. ## NameCheap (namecheap.com) ## use=web, web=dynamicdns.park-your-domain.com/getip protocol=namecheap, \ server=dynamicdns.park-your-domain.com, \ login=copper.com, \ password=<copy the password from namecheap advanced DN section> \ oxygen # myhost.namecheap.com The password to be provided above is what you will find on namecheap dashboard (Ref. Screenshot above). Log in to the namecheap account. Go to Advanced DNS Scroll down to Dynamic DNS section Copy the password Paste in ddclient config file Test DDCLIENT Before we schedule ddclient to run at boot, we need to test if it has been configured properly and is able to communicate with Namecheap by sudo ddclient -daemon=0 -debug -verbose -noquiet. If it is configured properly, you will see a message similar to this as part of the final output. SUCCESS: updating oxygen: good: IP address set to 92.117.273.56 If it is not what you see, and more importantly, if you do not see last line as "Success", then there is something wrong with configuration and you must correct it before proceeding. If this test worked, we are ready to update the DDCLIENT service. Set up DDCLIENT to run at start-up When we install ddclient using dnf, a ddclient.service file is automatically created in the location /etc/systemd/system/ddclient.service with following content. [Unit] Description=A Perl Client Used To Update Dynamic DNS After=syslog.target network.target nss-lookup.target [Service] User=ddclient Group=ddclient Type=forking PIDFile=/var/run/ddclient/ddclient.pid EnvironmentFile=-/etc/sysconfig/ddclient ExecStartPre=/bin/touch /var/cache/ddclient/ddclient.cache ExecStart=/usr/sbin/ddclient $DDCLIENT_OPTIONS [Install] WantedBy=multi-user.target We will enable and start this service by issuing following commands: sudo systemctl enable ddclient.service sudo systemctl start ddclient.service One would think that enabling and starting this service is all you need to do but that is not usually the case. I was getting following error: /bin/touch: cannot touch `/var/cache/ddclient/ddclient.cache': Permission denied A quick google search establishes that there seems to be some bug in how the ddclient cache file is created and how the permissions are set. After lot of searching, scratching head later, I did the following which fixed the issue. So if sudo systemctl start ddclient results in error, you may need to do the following: #Go Root su #Create a directory for ddclient mkdir /var/run/ddclient #Chown the various directories for ddclient as user chown ddclient:ddclient /etc/ddclient.conf chown ddclient:ddclient /var/run/ddclient/ #change directory cd /var/run/ddclient #delete ddclient.cache if it exists rm ddclient.cache #change directory cd /etc/sysconfig #delete ddclients.cache rm ddclients.cache #create a blank ddclient.cache nano /var/run/ddclient/ddclient.cache #chown it for ddclient user chown ddclient:ddclient /var/run/ddclient/ddclient.cache #exit root exit #enable and start ddclient service sudo systemctl enable ddclient.service sudo systemctl start ddclient.service Done !!! Known Issue with DDCLIENT There is a known issue and I can confirm that I have seen on my logfile as recently as today. WARNING: cannot connect to dynamicdns.park-your-domain.com:80 socket: IO::Socket::INET: Bad hostname 'dynamicdns.park-your-domain.com' It isn't major but it is there and restarting the service by issuing the command sudo systemctl restart ddclient.service fixes the problem.

DDCLIENT set-up on Fedora for Namecheap

Steps: Configure Namecheap Set-up DDCLIENT Test DDCLIENT Set up DDCLIENT to run at start-up Known Issue with DDCLIENT Configure Namecheap Follow the Namecheap guide here NOTE: For a subdomain "oxygen.copper.com", just replace @ with "oxygen" Set-up DDCLIENT #Install DDCLIENT on Fedora sudo dnf install ddclient #Edit the configuration file
I wrote this function to cater for a specific requirement and I don't know if there are better ways of doing it but this saved tremendous amount of time and might have real time application elsewhere. Problem Statement: Find out the age of an incident in working minutes, given the following: Time and Date of when an incident was logged Time and date of when the same incident was closed Opening time of the site for which the incident was logged Closing Time of the site for which incident was logged Country of the site for which incident has been logged Assumption: It is assumed that opening and closing times are same on all working days and that all the sites are closed on holidays and weekends Function should take all the above five "given" as parameter and then calculate age of the incident. Example of problem Let's say an incident was logged on "Friday 10th June 2016 at 12:00" for a site in the "UK" which opens between 09:00 to 16:00. This incident was then closed on "Tuesday 14th June 2016 at 14:00". For the above incident function should calculate the age as 960 minutes = 16 hours = [4 hours on Friday (12:00 to 16:00) + 7 hours on Monday (09:00 to 16:00) + 5 hours on Tuesday (09:00 to 14:00)] Pre-requisites: A holiday table for the "Country" of the site for which incident is being provided should already be created on the database with the name holiday_table. Created using code below: CREATE TABLE `holiday_table` ( `holiday_table_id` INT(11) NOT NULL, `holiday_date` DATETIME NULL DEFAULT NULL, `week_day` VARCHAR(12) NULL DEFAULT NULL, `holiday_name` VARCHAR(45) NULL DEFAULT NULL, `Country_codes` VARCHAR(45) NOT NULL DEFAULT 'ALL', PRIMARY KEY (`holiday_table_id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; Sample sql-insert Data for holiday_table: INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (2, '2016-03-25 00:00:00', 'Friday', 'Good Friday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (3, '2016-03-28 00:00:00', 'Monday', 'Easter Monday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (4, '2016-05-02 00:00:00', 'Monday', 'Early May bank holiday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (5, '2016-05-30 00:00:00', 'Monday', 'Spring bank holiday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (6, '2016-08-29 00:00:00', 'Monday', 'Summer bank holiday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (7, '2016-12-26 00:00:00', 'Monday', 'Boxing Day', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (8, '2016-12-27 00:00:00', 'Tuesday', 'Christmas Day (substitute day)', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (9, '2016-01-01 00:00:00', 'Friday', 'New Year’s Day', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (10, '2016-02-08 00:00:00', 'Monday', 'Chinese New Year', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (11, '2016-02-09 00:00:00', 'Tuesday', 'Chinese New Year', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (12, '2016-05-21 00:00:00', 'Saturday', 'Vesak Day', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (13, '2016-07-06 00:00:00', 'Wednesday', 'Hari Raya Puasa', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (14, '2016-08-09 00:00:00', 'Tuesday', 'National Day', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (15, '2016-09-12 00:00:00', 'Monday', 'Hari Raya Haji', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (16, '2016-10-29 00:00:00', 'Saturday', 'Deepavali', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (17, '2016-01-01 00:00:00', 'Friday', 'New Year’s Day', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (18, '2016-01-26 00:00:00', 'Tuesday', 'Republic Day', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (19, '2016-07-06 00:00:00', 'Wednesday', 'Idul Fitr', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (20, '2016-08-15 00:00:00', 'Monday', 'Independence Day', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (21, '2016-10-11 00:00:00', 'Tuesday', 'Dussehra/Durga Puja', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (22, '2016-10-31 00:00:00', 'Monday', 'Diwali Privilege Holiday/Gobardhan Puja', 'IN'); Function code and explanation CREATE DEFINER=`root`@`localhost` FUNCTION `workday_time_diff_holiday_table`( `param_country` varchar(10), `assigneddatetime` varchar(20), `closeddatetime` varchar(20), `starttime` varchar(20), `endtime` varchar(20) ) RETURNS int(11) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN Set @starttime = starttime; Set @endtime = endtime; Select time_to_sec(timediff(@endtime,@starttime))/3600 into @maxhoursaday; Set @assigneddate = assigneddatetime; Set @closeddate = closeddatetime; Set @timecount = 0; Set @timevar1 = @assigneddate; Set @nextdate = @assigneddate; Set @timevar2 = null; Set @param_country = param_country; ############ /*Check if the assigned time was before the starttime or closed time was after the endtime provided*/ ############# Set @checkstart = null; Set @checkend = null; Select CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@starttime), CONCAT(SUBSTRING_INDEX(@closeddate, ' ', 1), ' ',@endtime) into @checkstart, @checkend; if (@assigneddate > @checkstart) then if (@closeddate<@checkend) then Set @assigneddate = @assigneddate; Set @closeddate = @closeddate; else Set @assigneddate = @assigneddate; Set @closeddate = @checkend; end if; else if (@closeddate<@checkend) then SET @assigneddate = @checkstart; Set @closeddate = @closeddate; else SET @assigneddate = @checkstart; Set @closeddate = @checkend; end if; end if; #################### /*After above check, the assigneddate and closeddate variables will be reset in accordance with the checks.*/ #################################### SELECT DATEDIFF(@closeddate, @assigneddate) INTO @fixcount; # check the difference between assigned date and closed date. Set @count = @fixcount; # allocate the difference between closed date and assigned date to a counter If @fixcount > 0 then # true if line 57 resulted in more than 1 then run the while loop on next line while @count>=0 do # run the while loop until the count which is right now difference between closed and assigned becomes zero select weekday(@nextdate) into @weekday; # Assign the weekday value to @weekday. Weekday returns o for Monday, 2 for Tuesday ...5 for Saturday and 6 for Sunday /*Check if the date stored in nextdate (which is assigneddate on first run of while loop and closeddate on last run) is a holiday and set the holiday flag*/ Select sum(if(date_format(holiday_date,'%Y-%m-%d') = substring_index(@nextdate,' ',1),1,0)) from holiday_table where Country_codes = 'ALL' or instr(Country_codes,@param_country)>0 into @holidayflag; if ( @weekday<5 and @holidayflag=0) then #Proceed if the date in nextdate variable is neither weekend nor a holiday if (@count = @fixcount) then #Check if it is first run.ie. if nextdate is assigneddate Set @timevar1 = @assigneddate; #assign assigndate to variable timevar1 SELECT CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@endtime) INTO @timevar2;#get site closing time on assigned date and store it on to timevar2 elseif (@count = 0) then #if the date in nextdate variable is closeddate then do the following otherwise proceed Select concat(substring_index(@closeddate,' ',1),' ',@starttime) into @timevar1; # Set @timevar2 = @closeddate; else Select concat(@nextdate,' ',@starttime) into @timevar1; SELECT CONCAT(@nextdate, ' ', @endtime) INTO @timevar2; end if; SELECT LEAST(Greatest(((TIME_TO_SEC(TIMEDIFF(@timevar2, @timevar1))) / 3600),0),@maxhoursaday) INTO @timecounttemp; Set @timecount = @timecounttemp + @timecount; end if; Set @timevar1 = @nextdate; SELECT ADDDATE(SUBSTRING_INDEX(@timevar1, ' ', 1),1) INTO @nextdate; Set @count = @count - 1; end while; else #check if the assigned date / closed date is a holiday or weekend select weekday(@assigneddate) into @weekday; # Assign the weekday value to @weekday. Weekday returns o for Monday, 2 for Tuesday ...5 for Saturday and 6 for Sunday Select sum(if(date_format(holiday_date,'%Y-%m-%d') = substring_index(@assigneddate,' ',1),1,0)) from holiday_table where Country_codes = 'ALL' or instr(Country_codes,@param_country)>0 into @holidayflag; #Check if the date stored in assigneddate is a holiday and set the holiday flag if ( @weekday<5 and @holidayflag=0) then #Proceed if the date in assigneddate variable is neither weekend nor a holiday SELECT Least(Greatest(((TIME_TO_SEC(TIMEDIFF(@closeddate, @assigneddate))) / 3600),0),@maxhoursaday) INTO @timecount; else Set @timecount = 0; end if; end if; RETURN @timecount*60; END Calling the function Function will expect 5 parameters and with specific format as explained below: param_country - This is the country code as specified in holiday table assigneddatetime - This must be provided in the format %Y-%m-%d %H-%i-%s. So for our example it will be 2016-06-10 12:00:00 closeddatetime - This must be provided in the format %Y-%m-%d %H-%i-%s. So for our example it will be 2016-06-14 14:00:00 starttime - This must be in the format %H:%i. So for our example it will be 09:00 endtime - This must be in the format %H:%i. So for our example it will be 16:00 The call for this function will be as below: ## To get number of minutes Select `WORKDAY_TIME_DIFF_HOLIDAY_TABLE`('UK','2016-06-10 12:00:00','2016-06-14 14:00:00','09:00','16:00'); ## To get number of hours Select `WORKDAY_TIME_DIFF_HOLIDAY_TABLE`('UK','2016-06-10 12:00:00','2016-06-14 14:00:00','09:00','16:00')/60; ## To get in number of working days Select (`WORKDAY_TIME_DIFF_HOLIDAY_TABLE`('UK','2016-06-10 12:00:00','2016-06-14 14:00:00','09:00','16:00')/60)/(substring_index('16:00',':',1)-substring_index('09:00',':',1)); Complete Flowchart This chart was made using PlantText Chart Code @startuml scale 0.8 'skinparam monochrome true skinparam handwritten true skinparam shadowing false 'title Workday Time Difference Function start :Get the parameter values from calling function - assigneddatetime in 'YYYY-MM-DD hh:mm' format, - closeddatetime in 'YYYY-MM-DD hh:mm' format, - starttime in 'hh:mm' format, - endtime in 'hh:mm' format; :Set @starttime = starttime Set @endtime = endtime Set @assigneddate = assigneddatetime Set @closeddate = closeddatetime Set @timecount = 0 Set @timevar1 = @assigneddate Set @nextdate = @assigneddate Set @timevar2 = null; :Select time_to_sec(timediff (@endtime,@starttime))/3600 into @maxhoursaday; note left #TIMEDIFF calculates difference between two times #Time_to_sec function converts data into seconds #Dividing by 3600 converts the time into hours end note :Set @checkstart = null Set @checkend = null; :Select CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@starttime), CONCAT(SUBSTRING_INDEX(@closeddate, ' ', 1), ' ',@endtime) into @checkstart, @checkend; if (@assigneddate > @checkstart) then (yes) if (@closeddate0?) then (yes) while (count>=0?) is (true) :calculate weekday for nextdate select weekday(@nextdate) into @weekday; :Select sum(if(date_format(holiday_date,'%Y-%m-%d') = substring_index(@nextdate,' ',1),1,0)) from holiday_table where Country_codes = 'ALL' or instr(Country_codes,@param_country)>0 into @holidayflag; note left Set the holiday flag to anything other than zero if the value in nextdate for this loop is a holiday end note if ( @weekday0 into @holidayflag; note left Set the holiday flag to anything other than zero if the value in nextdate for this loop is a holiday end note if ( @weekday

MySQL function to calculate elapsed working time

I wrote this function to cater for a specific requirement and I don't know if there are better ways of doing it but this saved tremendous amount of time and might have real time application elsewhere. Problem Statement: Find out the age of an incident in working minutes, given the