Skip to content

SQL

MySQL Function to calculate closing WorkDay date given elapsed working time

On my post MySQL function to calculate elapsed working time I was asked in comments if the assumptions can be reversed such that given start date, starting time and closing time of site and the elapsed working hours, function should return the closed date. I was convinced that it will be possible to achieve this with minor tweaks to original logic and so I only concentrated on original code and how to tweak it to achieve the result. It is very likely that there might exist a more elegant solution but frankly I did not have a usecase I did find a usecase afterall - Clue was in redefining the problem description for this. Presented below is the function that with my minimal testing seems to give correct results. Feel free to try it and as always any feedback is welcome. 😄

Problem Description:

Find out the WorkDay date by when a ticket must be closed given:

  1. Country of site for which incident was logged.
  2. Time and Date when Incident was logged.
  3. Elapsed working hours in decimal. (Ten and a half hours as 10.5 and so on)
  4. Opening time of the site for which the incident was logged.
  5. Closing Time of the site for which incident was logged.
  6. SLA Type - Is the output date to be based purely on number of hours or based on SLA in days (eg: Next Business Day).

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.

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 following:

  1. Time and Date of when an incident was logged
  2. Time and date of when the same incident was closed
  3. Opening time of the site for which the incident was logged
  4. Closing Time of the site for which incident was logged
  5. 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.

Seafile Server behind nginx on Fedora 24 Security Lab Spin

I have recently been intrigued by the idea of replacing the likes of “Dropbox” and “Google Drive” with a cloud set-up of my own. I had "Owncloud" set-up for nearly a year but was not happy with it. There were minor niggles aside form speed and thumbnails and then “Owncloud” had a recent split leading to creation of “Nextcloud”.

While “Nextcloud” is the one that is more aligned to the general principles of community driven software, it is new and is still plagued with owncloud issues as it is essentially same stuff in new packaging at the moment.

In the meantime, every now and then I was reading all the good stuff people had to say about "Seafile" and so I wanted to give it a try. Now for the past year and a half I have also been using Fedora Security Lab spin on my home server and I just wanted to get the Seafile set-up on it so I did a few “duckduckgo” searches on the net and finally had the steps to achieve the objective. Obviously it all worked and my Seafile server is live and kicking, hence the post. 😄

So in nutshell my objective was to:

Install seafile-server-5 behind nginx on Fedora 24 Security Lab spin all on a 32 bit 12 year old laptop.

The steps I followed are listed below with detailed notes of what I did. I do not claim these to be perfect but this is what worked for me. If you know that something can be done better, please do let me know in the comments.