MySQL - Overlapping Date Range Filter

October 15, 2015 - I was bored so I went to play around with MySQL and solve some simple problem or exercise below to energize my playful mind.

I have a table for bugs from a bug tracking software; let’s call the table “bugs”. The table has four columns (id, open_date, close_date, severity). On any given day a bug is open if the open_date is on or before that day and close_date is after that day. For example, a bug is open on “2012-01-01”, if it’s created on or before “2012-01-01” and closed on or after “2012-01-02”. I want a SQL to show number of bugs open for a range of dates.

CREATE TABLE bugs (
  id INT,
  severity INT,
  open_date DATE,
  close_date DATE
);

INSERT INTO bugs VALUES
  (
    1, 1,
    STR_TO_DATE('2011-12-31', '%Y-%m-%d'),
    STR_TO_DATE('2011-12-31', '%Y-%m-%d')
  ),
  (
    2, 1,
    STR_TO_DATE('2012-01-01', '%Y-%m-%d'),
    STR_TO_DATE('2012-01-02', '%Y-%m-%d')
  ),
  (
    3, 1,
    STR_TO_DATE('2012-01-03', '%Y-%m-%d'),
    STR_TO_DATE('2012-01-03', '%Y-%m-%d')
  ),
  (
    4, 1,
    STR_TO_DATE('2012-01-03', '%Y-%m-%d'),
    STR_TO_DATE('2012-01-04', '%Y-%m-%d')
  ),
  (
    5, 1,
    STR_TO_DATE('2012-01-06', '%Y-%m-%d'),
    STR_TO_DATE('2012-01-07', '%Y-%m-%d')

  );

MySQL - Function to Capitalize First Letter of a Word

October 15, 2015 - I was bored so I went to play around with MySQL and solve some simple problem or exercise below to energize my playful mind.

Write a function to capitalize the first letter of a word in a given string.

Example: initcap(UNITeD states Of AmERIca ) = United States Of America

CREATE TABLE names (name VARCHAR(250));

INSERT INTO names (name) VALUES

  ("uNited states oF americA");

MySQL - Rank Order by Votes

October 15, 2015 - I was bored so I went to play around with MySQL and solve some simple problem or exercise below to energize my playful mind.

Write a query to rank order the following table in MySQL by votes, display the rank as one of the columns.

CREATE TABLE votes ( name CHAR(10), votes INT );

INSERT INTO votes VALUES
  ('Smith',10),
  ('Jones',15),
  ('White',20),
  ('Black',40),
  ('Green',50),
  ('Brown',20);

Cagayan de Oro - Software Freedom Day 2015

September 19, 2015 - A community organized event, Software Freedom Day, for the community in Cagayan de Oro City was held at Capitol University Laboratories.

It has been a tradition that ITGx together with Capitol University to organize and celebrate SFD every year along with various walk-in guests and invited guests from other school organization. Farthest was guests from Ozamis City which is approximately 5 hours away from the venue.

The goal of the event is to educate the public about the benefits of using high quality Free and Open Source Software (FOSS), Cagayan de Oro's Software Freedom Day celebration is filled with talks on various technologies from some of the leading developers in CdeO.


I had participated in the event as a resource speaker to talk about Play Framework fundamental and basics. Day after the event, I had a conversation with one of the audience and its good to hear that I am helping people learn to easily solve problems. Feels great.

Bellow was the schedule of events during that day and it was full of session everyone could learn.

8:00AM - 8:45AM	Registration and Sign up
8:45AM - 9:20AM	Opening program Invocation by CS-SBO President National Anthem Dr. Karen Joie Cuenca IT Supervisor, CHED-REGION 10 Keynote Speech
Morning Sessions	9:30AM - 11:45AM
Virtual Reality By Geoff Diaz and Edmund Madrid Salcedo Jr. Edge Interactive LAB A
Python Fundamentals By ITG-PyTsada with Arvin Vincent Simon from Innovuze LAB B
Introduction to Play Framework for other Devs By Paul Labis Mangium Software LAB C
Open Source DevOps Tools By Romar Mayer Micabalo SysAdmin, Innovuze LAB D
Lunch Break
Afternoon Sessions	1:30PM - 3:45PM
Introduction to Haskell By JR Requiroso, RedLemon Digital Media LAB A
Python Flask By ITG-PyTsada with Romar Mayer Micabalo from Innovuze LAB B
The IoT Stack: A Dissection: Arduino, Python, MQTT and everyTHING By Aryan Limjap and Jay Ginete, Capitol University LAB C
PHP The Right Way By Rudenyl Betonio, Innovuze LAB D
3:45PM - 4:00PM
Closing Ceremonies Signing of event Plaque Group picture taking

Hoping for next years Software Freedom Day.

Information Technology Group X - Balingasag Tech Talk

August 25, 2015 - A technology event was held in Misamis Oriental Institute of Science and Technology (MOIST) organized by host school in partnership with Information Technology Group X (ITGx). Supported by Google Developers Group - CDO, DevCon, Google Business Group and other companies locally like Mangium Software, Cleversoft and Innovuze Solutions.

The event concluded successfully that is able to transfer useful knowledge on various technologies commonly used and practiced by developers, system administrators and DevOps. Most of the audience are college students and faculties members. 

It was a fun ride for about 1 hour and 30 minutes going to the venue from city proper. Effort was worth it specially when you hear audience saying they learn so much from the event that they can use for their projects. ITGx organized great and passionate pool of speakers with over 30+ years of total experience in technology industry. 

Below is a picture giving ITGx token of appreciation to MOIST for hosting and organizing the event. Special thanks to  Felixberto S. Sambere and Tahir Aziz for a successful, fun filled out-of-town, first of its kind ITGx version of technology tech talk event

I hope to go back there someday and do a talk on technologies I love to work on. Hopefully next year we do another one. :)

Fix Inverted Camera on Ubuntu Linux

I am running Lucid Lynx 64 bit OS version on my Asus i3 K52J Series laptop. Like everyone else, I like using Skype video/chat as well as Cheese camera for taking pictures and videos. Not until then when I had a problem using it.

NOTE: Not advisable to do "export LIBV4LCONTROL_FLAGS=3", LIBV4LCONTROL_FLAGS is for debugging purposes only. - by Hans De Goede(libv4l author and maintainer)

I was so disappointed that my camera display was inverted! I tried reading several blogs and posting comments on Ubuntu forum threads online for days and but none of their solutions helped solved my problem. It could have been easily solved if only there was an option to invert my camera on Video4Linux Device Preference. Sadly, there was not! So, I decided to find a way to flip the camera by looking into export options on gtk-v4l(Video4Linux). 

Backup or Restore PostgreSQL Database

There is an easy way to create backup file and restore your current PostgreSQL database using the Linux terminal. I am not sure though if this is the easiest way.

As of the moment I am writing this article, I used the following command to backup and restore my database. 

Using pg_dump and psql will make your life easier. Below are the terminal command I use to backup and restore my database.

• Backup : pg_dump -U {user_name} {database_name} -f {backup_file_name}
• Restore : psql -U {user_name} -d {database_name} -f {backup_file_name}

If you are getting an error: