MySQL Cookbook

Writing Date-Processing Utilities

Problem

There’s a given date-processing operation that you need to perform frequently, so you want to write a utility that does it for you.

Solution

The utilities in this section provide some examples showing how to do that.

Discussion

Due to the idiosyncratic nature of dates, you’ll probably find it necessary to write date converters from time to time. This section shows some sample converters that serve various purposes:

• isoize_date.pl reads a file looking for dates in U.S. format (MM-DD-YY) and converts them to ISO format.

• cvt_date.pl converts dates to and from any of ISO, US, or British formats. It is more general than isoize_date.pl, but requires that you tell it what kind of input to expect and what kind of output to produce.

• monddccyy_to_iso.pl looks for dates like Feb. 6, 1788 and converts them to ISO format. It illustrates how to map dates with non-numeric parts to a format that MySQL will understand.

All three scripts are located in the transfer directory of the recipes distribution. They assume datafiles are in tab-delimited, linefeed-terminated format. (Use cvt_file.pl to work with files in a different format.)

Our first date-processing utility, isoize_date.pl, looks for dates in U.S. format and rewrites them into ISO format. You’ll recognize that it’s modeled after the general input-processing loop shown in Recipe 10.21, with some extra stuff thrown in to perform a specific type of conversion:

#! /usr/bin/perl -w # isoize_date.pl - Read ...