I spent quite some time this afternoon finding a reliable way of converting Unix Time Stamps (UTS) to the ISO 8601 format used in SQL databases like MySQL and PostgreSQL that does not get confused by timezones. In the end the final result is, as is so often the case with Perl, very short and simple, but since the Googles failed to find the answer for me today, I thought it would be worth sharing in case it’s of use to others in the future.

[perl]
use DateTime;

# Function to convert Unix Time Stamp to ISO 8601 Date
# Arguments:
# 1) The Unix Time Stamp to convert
sub uts_to_iso8601{
my $uts = shift;
my $date = DateTime->from_epoch(epoch => $uts, time_zone => ‘UTC’);
return $date->ymd().q{ }.$date->hms().’z’;
}
[/perl]

The algorithm is very simple, use DateTime‘s from_epoch function to a DateTime object in the UTC timezone (AKA Zulu). Then assemble the output as YYYY-MM-DD HH:MM:SS, and append a z for Zulu. MySQL and PostgreSQL can now use the string to populate Date or Timestamp columns.

As an example, the Unix Time Stamp 1369410796 converts to 2013-05-24 15:53:16z.

Tagged with:

I ran into a problem this week when the perl libraries for accessing MySQL databases refused to work on Mac OS X Lion. I did a ‘regular’ install:

  1. Install the 64bit version of MySQL Community Server (being sure to use the .dmg version so as to get the .pkg installer)
  2. use CPAN to install the needed database libraries:
    1. $ perl -MCPAN -e shell
    2. cpan[1]> install Bundle::DBI
    3. cpan[1]> install DBD::mysql

There were no errors during the install, so I assumed all we well, until I tried to actually use the libraries to access a database that is! Using DBI to try connect to a MySQL database gave the following error:

[code]
install_driver(mysql) failed: Can’t load ‘/Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle’ for module DBD::mysql: dlopen(/Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle, 1): Library not loaded: libmysqlclient.18.dylib
Referenced from: /Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle
Reason: image not found at /System/Library/Perl/5.12/darwin-thread-multi-2level/DynaLoader.pm line 204.
[/code]

After a lot of googling I found plenty of people with the same problems, including people on Snow Leopard, so I figured this was not a new problem. I tried a number of the suggested solutions, and most did not work, but after two days of trying, I found one that did, and it was wonderfully simple!

The problem is that the MySQL libraries are not in the OS’s library path, so they are not being found, most of the proposed solutions tried to tackle the problem at compile time, or to use simlinks to hack the libraries into the path, but like I say, these solutions didn’t work for me. What did work is simply updating the library path in my environment!

If you run the command below before executing your Perl script the library is found and all is well!

export DYLD_LIBRARY_PATH="/usr/local/mysql/lib:$DYLD_LIBRARY_PATH"

It’s a little awkward to do this all the time, so I added the line to my ~/.bash_profile file, and now it just works for me!

I spent two frustrating days trying to fix this, so hopefully I’ll save others some time by sharing my solution.

Tagged with:

In general the move to Leopard has been very smooth for me but there was one notable exception, getting a working PostgreSQL 8 sever up and running on my MacBookPro. A few weeks ago I’d expended a lot of time and effort on this and gotten nowhere so at that stage I’d decided to work around the problem by using a remote PostgreSQL server rather than running one locally. This works fine as long as you have broadband internet access. However, I’m off to Belgium for a week on Saturday where I’ll have no broadband but where I will need to get some development work done that requires access to a PostgerSQL server.

With the end of the week getting closer I had another go at getting PosgreSQL to behave on OS X and I did eventually succeed. I got 90% of the way there by following these instructions but then I ran into a few strange problem that took a little more time and effort to work around. However, the good news is that I got it all figured out in the end!

[tags]PostgreSQL, OS X, OS X 10.5 Leopard[/tags]

Read more

Tagged with:

It’s not uncommon that as part of a complex transaction you need to insert a row into a table with an auto-incrementing primary key and then use the key from the row you just generated as a foreign key in another insert within the same transaction. Java provides a mechanism to return the auto-generated keys from an insert query without the need to execute a second query by means of the function java.sql.Statement.getGeneratedKeys(). Unfortunately the PostgreSQL JDBC connector does not appear to support this feature (at least the version I am using does not). Given the fact that we do not have this functionality available to us when using a PostgreSQL database we need to use a separate query to retreive the value of the key we just generated. It goes without saying that we need to do this two-step process as part of a transaction but it may not go without saying that the correct way to extract the value for the key is by querying the sequence that the key is being generated from and not by querying the table directly. It is true that most of the time the highest value of the incrementing variable will be the value just inserted but that is not a guarantee.

Read more

Tagged with:

Having recently discussed running MySQL on the Mac I’d now like to tackle PostgreSQL. I’m sorry to say things are not quite as rosy just yet. PostgreSQL don’t provide us with a binary distribution for the Mac nor do they provide a nice Panel for the System Preferences App. There are also less choices when it comes to GUIs for manipulating and designing your database but there are still options and things are still a lot better than they could be. Read more

Tagged with: