Date Modules

There are a large number of Perl modules that you can use to format and convert data that represents dates. Date handling has traditionally been a challenge for Oracle DBAs and developers. The fact is that dates are, well, confusing. After 100,000 years of Neolithic Sky watching, with some heavy input from the Babylonians, dates have become more twisted in their logic than a boat full of lawyers arguing over a politician's expense account. Although Oracle provides a number of built-in functions for date handling (TO_DATE, TO_CHAR, etc.), you may find these functions cumbersome or inefficient. This is particularly true if you're working with time intervals (NUMTODSINTERVAL, NUMTOYMINTERVAL, TO_DSINTERVAL etc.). The Perl data modules described in this section provide easier ways to handle data conversion. You will also find them helpful if you simply want to pre-clean data in Perl before overloading the Oracle SQL engine with calls to Oracle's own date functions.

The date- related modules listed in Table D-3 are available on both CPAN (for Unix) and ActiveState (for Win32). Some of them are dependent on each other, so we've listed them out in the appropriate installation order (least dependent first). Some also require additional modules, which are listed in Table D-3 (also in installation order wherever possible).

Table D-3. Date-based modules

CPAN module	Description/CPAN address
Date::Business	Written by Richard DeSimine; calculates business dates. http://www.cpan.org/authors/id/D/DE/DESIMINER
Date::Calc	Written by Steffen Beyer; a C-based date formatting masterpiece, described in detail in the following section. http://www.cpan.org/authors/id/STBEY
Date::Pcalc	Written by J. David Eisenberg; a pure Perl version of Date::Calc . http://www.cpan.org/authors/id/STBEY
Date:: Christmas	Written by Elaine M. Ashton; returns Christmas day for any Gregorian year following 1600 AD ” for example, christmasday(2002) => Wednesday . http://www.cpan.org/authors/id/H/HF/HFB
Date:: Decade	Written by Michael Diekmann; provides decade-based date calculations; relies on either Date::Calc or Date::Pcalc . http://www.cpan.org/authors/id/M/MI/MIDI
Date::Easter	Written by Rich Bowen; requires several extra modules, listed in Table D-4. Date::Easter provides both Gregorian and Orthodox Easter information. http://www.cpan.org/authors/id/RBOW
Date::Handler	Written by Benoit Beausejour; handles time zones and locales. http://www.cpan.org/authors/id/B/BB/BBEAUSEJ
Date::Japanese::Era	Written by Tatsuhiko Miyagawa; converts dates between the Japanese Era and Gregorian calendar; requires two modules, listed in Table D-4. http://www.cpan.org/authors/id/M/MI/MIYAGAWA
Date::Simple	Written by John Tobey; this speed-driven module validates dates, calculates date-time intervals, performs day-of-week arithmetic, and much more. http://www.cpan.org/authors/id/JTOBEY
Date::Range	Written by Tony Bowden; calculates date ranges and analyzes date patterns; relies on Date::Simple and Test::Simple (see Table D-4). http://www.cpan.org/authors/id/T/TM/TMTM
Date::Manip	Written by Sullivan Beck; a pure-Perl module for dates and times, which is recommended when the faster Date::Calc fails to provide the required options or when you need some really clever date string parsing. Date::Manip is the Daisy-Cutter date module in the Perl world; when all else fails, you can rely on Date::Manip to provide that extra bit of functionality. http://www.cpan.org/authors/id/SBECK
DateTime::Precise	Written by Blair Zajac; this object-oriented module deals with the usual date and time suspects , plus GPS operations and fractional seconds. http://www.cpan.org/authors/id/B/BZ/BZAJAC

Table D-4. Required modules for date-based formatting

CPAN module	Reliant module	Description/CPAN address
Mime::Base64	Date::Japanese::Era	Written by Gisle Aas; used for Base64 strings. http://www.cpan.org/authors/id/GAAS
Jcode	Date::Japanese::Era	Written by Dan Kogai; code for the Japanese character set. http://www.cpan.org/authors/id/D/DA/DANKOGAI
Devel ::CoreStack	Date::Easter	Written by Alligator Descartes; used for debuggers . http://www.cpan.org/authors/id/ADESC
Test::Harness	Date::Easter	Written by Michael G. Schwern; a test harness for Perl modules. http://www.cpan.org/authors/id/MSCHWERN
Test::Simple	Date::Easter, Date::Range	Also written by Michael G. Schwern; provides basic utilities for writing Perl tests.http://www.cpan.org/authors/id/MSCHWERN

In the following sections we'll look at Date::Calc , the module we consider the most powerful in the Perl date munging world because of its high speed.

Date::Calc and Date::Calendar

Perl's most useful and efficient date formatting module is Steffen Beyer's Date::Calc . Although this module offers fewer methods than does the Date::Manip module, Date::Calc 's C library greatly enhances its munge processing speed. You can obtain this module from:

http://www.cpan.org/authors/id/STBEY

We'll also look at Date::Calenda r, which comes with Date::Calc and provides some handy methods for dealing with business calendars. To use Date::Calendar , you may have to install the Bit::Vector module, also available from Steffen Beyer's CPAN site.

For Win32 users, the latest Bit::Vector and Date::Calc versions are available from ActiveState (although Date::Calc is already pre-installed with ActivePerl):

 C:\>ppm PPM> install Bit-Vector PPM> install Date-Calc     # To get the latest version! :-) 

The Date-Calc-5.0 API

In the following list we've described every nondeprecated method in the Date::Calc 5.0 API:

Days_in_Year

The days in the year, up to the supplied month (1..12), in the given year:

 $days = Days_in_Year($year, $month); 

Days_in_Month

The number of days in a month for a given year. The year is required, although it's logically only necessary for February's leap-year variations:

 $days = Days_in_Month($year, $month); 

Weeks_in_Year

Fetches the number of weeks in a given year (either 52 or 53) (see Figure D-3):

Figure D-3. ISO 8601 ” Which year owns which week?

 $weeks = Weeks_in_Year($year); 

eap_year

Returns 1 for true, in a leap year, otherwise 0 for false:

 $leap_year_flag = leap_year($year); 

check_date

Returns 1 if the year, month, day combination is a real date, otherwise 0:

 $valid_date_flag = check_date($year, $month, $day); 

check_time

Returns 1 if the hour , minute, second combination is valid, otherwise 0:

 $valid_time_flag = check_time($hour, $min, $sec);  # 24 hour clock! :-) 

check_business_date

Returns 1, for valid business dates (e.g., Year 2002, Week 47, Day 3), otherwise 0:

 $valid_business_flag = check_business_date($year, $week, $day_of_week); 

Day_of_Year

Returns the year day from 1. . . 366 (with 366 for leap years):

 $day_of_year = Day_of_Year($year, $month, $day); 

Date_to_Days

Starting from 1 Jan 1 AD, which is day one, ^[9] returns the number of days since that date, such that Date_to_Days(1, 1, 1) returns 1:

^[9] The Gregorian calendar goes from 31 Dec 1 BC, to 1 Jan 1 AD. There's no year zero.

 $days = Date_to_Days($year, $month, $day); 

Day_of_Week

Returns the weekday of the supplied date ( 1 = Monday, .., 7 = Sunday ):

 $weekday = Day_of_Week($year, $month, $day);  # Returns 1..7 

Week_Number

Returns the year's week number; Week_Number(2002, 12, 25) gives 52:

 $week = Week_Number($year, $month, $day); 

Week_of_Year

Using ISO 8601, decides which year owns a week split over a New Year cusp by calculating which year has the Thursday. The first week containing it (and therefore four days) is the first week in any year:

 ($week, $year) = Week_of_Year($year, $month, $day); 

Monday_of_Week

Generates the date on the first day of the given year's week:

 ($year, $month, $day) = Monday_of_Week($week, $year); 

Nth_Weekday_of_Month_Year

For recurring dates. You can calculate the third Tuesday's date in May, using Nth_Weekday_of_Month_Year(2003, 5, 2, 3) to return (2003, 5, 20) :

 ($year, $month, $day) =     Nth_Weekday_of_Month_Year($year,$month,$day_of_week,$nth_weekday); 

Standard_to_Business

Converts a given date to a business format of year, week, and day:

 ($year,$week,$day_of_week) = Standard_to_Business($year,$month,$day); 

Business_to_Standard

The dark half of Standard_to_Business . This performs a reverse operation:

 ($year,$month,$day) = Business_to_Standard($year,$week,$day_of_week); 

Delta_Days

The number of days between dates. A greater second date makes this positive:

 $diff_days = Delta_Days($year1,$month1,$day1,$year2,$month2,$day2); 

Delta_DHMS

The days, hours, minutes, and seconds difference between two date-times:

 ($diff_days, $diff_hours, $diff_mins, $diff_sec) =     Delta_DHMS($year1, $month1, $day1, $hour1, $min1, $sec1,                $year2, $month2, $day2, $hour2, $min2, $sec2); 

Add_Delta_DHMS

Performs complex date and time addition in many permutations , the most usual of which is to take a date and time, add on some differences, and then see what new date and time is generated:

 ($year, $month, $day, $hour, $min, $sec) =     Add_Delta_DHMS($year, $month, $day, $hour, $min, $sec,                    $diff_day, $diff_hour, $diff_min, $diff_sec); 

Delta_YMD

Creates an array: ($year2 -- $year1,$mnth2 -- $mnth1,$day2 -- $day1) :

 ($diff_year, $diff_mnth, $diff_day) =     Delta_YMD($year1, $mnth1, $day1, $year2, $mnth2, $day2); 

Delta_YMDHMS

Similar to Delta_YMD , but with the extra time element:

 ($diff_year,$diff_month,$diff_day,$diff_hour,$diff_min,$diff_sec) =     Delta_YMDHMS($year1, $month1, $day1, $hour1, $min1, $sec1,                  $year2, $month2, $day2, $hour2, $min2, $sec2); 

Normalize_DHMS

Takes four different time elements, days, hours, minutes, and seconds, negative or positive relative to right now. It then combines them into a smoothed-out figure:

 use Date::Calc qw ( Normalize_DHMS ) ;    # Take away 3 days from right now, add on 120 hours, take away # 750 minutes, and add on 3645 seconds.  We should end up # with 1 day, 12 hours, 30 minutes and 45 seconds as the # smoothed out computed result, in relation to  right now  .    ($diff_day, $diff_hour, $diff_min, $diff_sec) =    Normalize_DHMS(-3, +120, -750, +3645);  # days, hours, mins, secs  # We're expecting 1 day, 12 hours, 30 minutes and 45 seconds! :-)  print "$diff_day day, $diff_hour hrs, $diff_min min $diff_sec sec\n"; 

Executing this code produces the following result:

 $ perl normalizeDHMS.pl  1 day, 12 hrs, 30 min, 45 sec  

Add_Delta_Days

Answers questions such as "What's the date 30 days from today?":

 ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $diff_day); 

Add_Delta_DHMS

Answers questions like "What's the date-time if we add on 30 hours?":

 ($year, $month, $day, $hour, $min, $sec) =     Add_Delta_DHMS($year, $month, $day, $hour, $min, $sec,                    $diff_day, $diff_hour, $diff_min, $diff_sec); 

Add_Delta_YM

Returns the date, when provided with a date, plus a year and month offset:

 ($year, $month, $day) =     Add_Delta_YM($year, $month, $day, $diff_year, $diff_month); 

Add_Delta_YMD

Extends Add_Delta_YM by allowing the addition of an offset days figure:

 ($year, $month, $day) =     Add_Delta_YMD($year,$month,$day,$diff_year,$diff_month,$diff_days); 

Add_Delta_YMDHMS

Another extension to Add_Delta_YMD , this time allowing a time offset:

 ($year, $month, $day, $hour, $min, $sec) = Add_Delta_YMDHMS(       $year,$month,$day,$hour,$min,$sec,        $diff_year,$diff_month,$diff_day,$diff_hour,$diff_min,$diff_sec); 

System_Clock

Returns the list of values displayed in Table D-5, with localtime( ) being used by default. An optional true flag calls gmtime ( ) instead, to get the GMT (Greenwich Mean Time) or UTC (Universal Time Coordinated), depending on your system: ^[10]

^[10] For a discussion of Julian dates and Julian days, try the following web page: http://aa.usno.navy.mil/data/docs/JulianDate.html

 ($year, $month, $day, $hour, $min, $sec, $Julian_day_of_year,     $day_of_week, $daylight_savings) = System_Clock([$gmt_flag]); 

Table D-5. Figures provided by Date::Calc's system_clock

Figure type	Range	Comments
Year	1970..2038+	Your OS determines the maximum value
Month	1..12	January = 1, .., December = 12
Day of month	1..31	Notice that this is not 0..n format, as with hours below
Hour	0..23	The 24-hour clock is used
Minute	0..59	Notice that this is not 1..60
Second	0..59	Range may be 0..61, to cope with leap seconds
Day of year	1..366	The 366 figure is for leap years
Day of week	1..7	Monday = 1, .., Sunday = 7
Daylight Savings	-1..1	-1 = daylight savings info unavailable, 0 = daylight savings currently out of use, 1 = daylight savings in use

Leap seconds slip into the calendar every 500 days or so at the end of December or June. Our globe spins 2 milliseconds a day slower than it did in 1900 because of the moon's tidal braking effect. Therefore, GMT gradually diverges from the atomic clocks measuring UTC. Leap seconds bring everything together again.

Note that tidal braking has already stopped the moon's face rotating relative to the Earth, giving rise to Pink Floyd's album, The Dark Side of the Moon . One day, a single face of the Earth will oppose a more distant Moon. However, by then the Sun will have expanded, giving us something even more interesting to experience ” a Floyd album called Jolly Red Giant perhaps?

For more information (not about Pink Floyd, promise), see: http://www.npl.co.uk/npl/ctm/leap_second.html.

Today

Returns a subset from System_Clock : the year, month and day:

 ($year, $month, $day) = Today([$gmt]); 

Now

Another System_Clock subset returns the current hour, minute, and second:

 ($hour, $min, $sec) = Now([$gmt_flag]); 

Today_and_Now

Returns the current year, month, day, hour, minute, and second:

 ($year, $month, $day, $hour, $min, $sec) = Today_and_Now([$gmt]); 

This_Year

Returns the current year:

 $year = This_Year([$gmt_flag]); 

Gmtime

Returns the GMT values displayed in Table D-6 according to the optional parameter, the number of seconds since midnight, 1 Jan 1970. This is the start of the Unix epoch . If absent, the current time( ) value is used:

 ($year, $month, $day, $hour, $min, $sec, $doy, $dow, $dst) =     Gmtime([$time_in_seconds_since_1970]); 

Localtime

The local time equivalent to Gmtime :

 ($year,$mnth,$day,$hour,$min,$sec,$doy,$dow,$dst) = Localtime([$time]); 

Mktime

Generates the number of seconds since the 1970 epoch:

 $time = Mktime($year, $month, $day, $hour, $min, $sec); 

Timezone

Generates differential time offsets between local time and GMT. Those to the east of Greenwich, England receive positive offsets. Those to the west receive negative ones. A daylight savings flag is also returned:

 ($diff_year, $diff_month, $diff_day,   $diff_hour, $diff_min, $diff_sec, $dst) = Timezone([$time]); 

Date_to_Time

This is similar to Mktime , but faster because it avoids system calls:

 $time = Date_to_Time($year, $month, $day, $hour, $min, $sec); 

Time_to_Date

Returns the GMT date-time values when supplied with the appropriate number of seconds since 1970. Uses the built-in time( ) function as the default:

 ($year, $month, $day, $hour, $min, $sec) = Time_to_Date([$time]); 

Easter_Sunday

Calculates the Gregorian Easter Sunday date for the years 1583 to 2299, via the Gauss algorithm. The original Easter was agreed to by the early Christians in 325 AD. This held firm until 1582 AD when the Gregorian Easter, which now differs from the Orthodox one, became the first Sunday following the first full moon preceding a Sunday after the Spring equinox:

 ($year, $month, $day) = Easter_Sunday($year); 

For Orthodox functionality, try the Date::Easter module in Table D-3.

Decode_Month

Requires a string to uniquely identify a month in the current Date::Calc language. For example, the parameters `N' , `nov', and `November' all return 11. Zero is returned if Decode_Month fails to work out the month:

 $month = Decode_Month($string); 

Decode_Day_of_Week

As with Decode_Month , a string able to identify a day will return 1 to 7:

 $day_of_week = Decode_Day_of_Week($string); 

Decode_Language

Returns Date::Calc 's internal ID for a supported language, if uniquely identified from a string. Otherwise, zero is returned. Eleven languages come automatically with Date::Calc , as detailed in Table D-6. Others can be added by following the instructions in INSTALL.txt :

 $lang = Decode_Language($string); 

Table D-6. Languages supplied with Date::Calc 5.0

Internal ID	Language	Comments/English translation
1	English	Default language for Date::Calc
2	Fran §ais	French
3	Deutsch	German
4	Espa ±ol	Spanish
5	Portugu s	Portuguese
6	Nederlands	Dutch
7	Italiano	Italian
8	Norsk	Norwegian
9	Svenska	Swedish
10	Dansk	Danish
11	Suomi	Finnish

Decode_Date_EU

One of the cleverest Perl functions we've ever seen. Feed it a string, with some kind of embedded date, and if Decode_Date_EU can identify three lucky numbers inside it, in the European date order of day, month, and year, it returns this list. An empty list is returned if no date can be found.

 ($year,$month,$day) = Decode_Date_EU($string); 

Decode_Date_US

Behaves identically to Decode_Date_EU above, except it tries to find a valid date in the North American date format of month, day, year:

 ($year,$month,$day) = Decode_Date_US($string); 

Fixed_Window

Takes a two-digit number and turns it into a four-digit year, dependent on a fixed window centered around 1970. All numbers from 70 to 99 are converted in the range 1970 to 1999. All numbers below 70 are converted upwards. For example, 69 goes to 2069 :

 $year = Fixed_Window($non_negative_number_less_than_100); 

Moving_Window

Imposes a 100-year window, cross-haired upon today's date, to go back 50 years and forward 50 years. The two-digit entry is initially mapped to the current century. If more than 50 years ago, 100 years are added to the total. If 50 years or more into the future, 100 years are taken off:

 $year = Moving_Window($non_negative_number_less_than_100); 

Date_to_Text

Translates year, month, and day into a short piece of text, dependent on the currently selected language. For example, with the English default language, Date_to_Text(2002, 12, 25) creates Wed 25-Dec-2002 :

 $string = Date_to_Text($year,$month,$day); 

Date_to_Text_Long

Provides a longer date-string, dependent on language; Date_to_Text_Long(2002,12,25) creates Wednesday, December 25th 2002 :

 $string = Date_to_Text_Long($year,$month,$day); 

English_Ordinal

Takes a cardinal number and turns it into an English ordinal abbreviation, so English_Ordinal(101) produces 101st :

 $string = English_Ordinal($number); 

Calendar

Produces a calendar string:

 $string = Calendar($year,$month[,$orthodox]); 

The optional $orthodox flag, if set to true, returns a calendar starting on a Sunday, rather than a Monday, so Calendar(2002, 12, 1) produces:

 December 2002 Sun Mon Tue Wed Thu Fri Sat   1   2   3   4   5   6   7   8   9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31 

Month_to_Text

Provides the full month name , in the current language, when supplied with a number in the range 1 to 12. Month_to_Text(11) outputs November :

 $string = Month_to_Text($month); 

Day_of_Week_to_Text

With a day range of 1..7 , Day_of_Week_to_Text(2) returns Tuesday :

 $string = Day_of_Week_to_Text($day_of_week); 

Day_of_Week_Abbreviation

Returns day of the week abbreviations, such as Mon for 1 :

 $abbrev_string = Day_of_Week_Abbreviation($day_of_week); 

Language_to_Text

When given a valid internal ID, returns the name of the language:

 $string = Language_to_Text($lang); 

Language

Works out the internal ID for the current language, or changes it:

 $lang = Language(  ); Language($lang); $oldlang = Language($newlang); 

Languages

Returns the total number of languages Date::Calc is currently supporting:

 $max_lang = Languages(  ); 

Parse_Date

Does its best to parse a date string for you:

 ($year, $month, $day) = Parse_Date($string); 

ISO_LC

Returns a string in which all ISO-Latin-1 characters are lower-cased:

 $lower = ISO_LC($string); 

ISO_UC

Returns a string in which all ISO-Latin-1 characters are upper-cased:

 $upper = ISO_UC($string); 

Version

And finally, this one provides the current version of Date::Calc ” for example, 5.0 :

 $string = Date::Calc::Version(  );