Some simple MySQL date-time functions

I often need the first of the month, the last of the month or how many days are in the month for reporting and forecasting purposes.

You can, of course, like myself in the past look up how these things are done every time you need them. But this gets frustrating and it’s often easier to surround yourself with your set of own SQL-functions that give you what you need and are easy to remember.

For this purpose I give you:

  1. First of the month date:
  2. drop function if exists fn_getFirstOfMonth;
    create function fn_getFirstOfMonth(date_ datetime) returns datetime
    set @dater = date_;
    set @dater = DATE_FORMAT(@dater ,’%Y-%m-01′);
    return @dater;

  3. Last of the month date:

    drop function if exists fn_getLastOfMonth;
    create function fn_getLastOfMonth(date_ datetime) returns datetime
    set @dater = date_;
    set @dater = DATE_FORMAT(last_day(@dater) ,’%Y-%m-%d’);
    return @dater;

  4. Days in the month:

    drop function if exists fn_getDaysInMonth;
    create function fn_getDaysInMonth(date_ datetime) returns int
    set @dater = date_;
    set @dater = day(last_day(@dater));
    return @dater;

Do let me know if there are other MySQL date and time functions that you would like to know.
Happy to post them here…

MySQL problems: Starting MySQL. ERROR! Manager of pid-file quit without updating file.

I stumbled across this error when I tried to import an InnoDB backup into my existing MySQL server.
I have one application that collects data that I need to tie to another set of data that is recorded on a different server through a different application.
It’s been rather challenging, cause I had to work of an InnoDB backup and they aren’t easily imported to another MySQL server, especially if you want to keep the data on that other server.

Okay, so what I tried, and ultimately prevented me from restarting MySQL was:

  1. changing all tables on the MySQL server to MyISAM
  2. killing MySQL (yes, I couldn’t stop it)
  3. copying the files in the MySQL DataDir to a backup location
  4. Copying the InnoDB backup files that I want to import into my DataDir
  5. trying to restart MySQL
But this is were the trouble started. The files couldn’t be imported and I had to roll back and use my backup files. But:

# sudo /etc/init.d/mysql start
Starting MySQL. ERROR! Manager of pid-file quit without updating file.

So I deleted the pointer file in /var/lock/subsys/

# sudo rm -f /var/lock/subsys/mysql
# sudo /etc/init.d/mysql start
Starting MySQL. ERROR! Manager of pid-file quit without updating file.

This didn’t work…
After long fiddling, I found out that the files in my DataDir have changed it’s owner, since I copied them as root to the backup location and back. They were now owned by root. So after:

# sudo chown mysql:mysql *
# sudo chgrp mysql *

I could do:

# sudo /etc/init.d/mysql start
 Starting MySQL. SUCCESS!

Hope this helps you too!

Connecting your MySQL data with MS Excel

Excel is still the most popular product when it comes to working with spreadsheets or analysing data. I often analyse large spreadsheets with data I got from a database. 

While it’s not too much effort to copy the data into the spreadsheet when you do need something ad hoc, it can be quite frustrating if you are preparing the same reports over and over again. It would be much easier to just integrate the query as a data source in Excel.
Excel has already got great features to tie into MSSQL, probably because they want to sell more of their own stuff, but I suppose it should be just as easy connecting it with MySQL…
  1. We will need a driver:
    MySQL provides a lot of connector files on their website, it’s pretty easy to navigate and find what you are looking for, but, just  in case, here is the link to the ODBC files I will be using for an Windows XP conncetion:
  2. We will need the data source as a ODBC connection in Windoze:
    Go Start > Control Panel > Administrative Tools > Data Sources (ODBC) > Add…
    and find the newly installed MySQL driver. Then it will ask you to enter your details and you should be half way there already…
  3. It’s already time to use Excel:
    so start Excel… There are now different ways of getting data into Excel. I want to use my own query, so I’m going to use the ‘From Microsoft Query’, as it recognises the existing ODBC connection we established.

    Then I’m going to connect to the ‘localhost’ connection I previously created:

    To get further, I need to select at least one field from the database. It doesn’t matter if it’s a relevant field, because a query tool will appear at the end of the dialogue.

    It comes in form of a question, and you select the lower radio button:

    Finally, you can add your own query (SQL button).
    And the result:

It will take a while when you enter your own query, very much depended on how complex your query is, but I hope for you it takes under five minutes to run 😀
Let me know if that works for you or if you have any breaking points. 

Java & MySQL select query to return a two dimensional array of results

(vector of arrays actually)

When I started to do some Java again, I noticed that Java doesn’t really have an easy way of getting an array from a query, if you don’t always want to declare how many columns and rows you expect. To circumvent this problem I have a class in my toolkit that contains a method that returns a vector of result lines and each element contains an array of results…

This makes it really easy then to just write a query, like

Vector  myResultVector = selectQuery("select * from customerdetails");

, and get the Vector of arrays back.

To illustrate, here is the method:

public Vector selectQuery(String query) throws SQLException {
Statement st = connection.createStatement();
ResultSet res = stat.executeQuery(query);

ResultSetMetaData rsmd = result.getMetaData();
numCols = rsmd.getColumnCount();

               // Optional print out:
//System.out.println("The query was executed. The number of columns returned is: " + numCols);

        //We don't return this here in the same method, but this variable is declared earlier and can be returned through a different method.
for (int i=1; i<=numCols; i++) {
            columnNames.add(i-1, rsmd.getColumnName(i));
           // System.out.println(columnNames.elementAt(i-1));

while ( {
String[] arr1 = new String[numCols];
for (int t=1; t <=numCols; t++) {
arr1[t-1] = result.getString(t);
return results;

In the constructor of the actual class, I have the database connection, looking something like this:

 public mysqlConnector() {

try {
String userName = "YOURUSER";
String passWord = "YOURPASSWORD";
String url = "jdbc:mysql://YOURSERVER/YOURSCHEMA";
Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            connection = DriverManager.getConnection (url, userName, passWord);
            System.out.println ("Database connection established!");
catch (Exception err1) {
System.err.println ("Cannot connect to database server!");

Maybe worth mentioning the global variables that I declare in this class:

 public Vector results = new Vector ();
public Vector columnNames = new Vector ();
public int numCols  = 0;

The results, because there are other methods that are using the same Vector, the columnNames and the numCols, because there is a method that returns them when I need them:

public Vector  getColumnHeaders(){
return columnNames;

public int getColumnNumber(){
return numCols;

It might also be good to close the database connection when you don’t need it anymore:

 public void disconnectionNotice() {
if (connection != null) {
try {
connection.close ();
System.out.println("Connection to DB closed!");
catch (Exception err2) {

Hope this helps some of you that might have struggled with the same problem. Email me if this isn’t all clear to you… Happy to help.