Using PerlThe DateCalc Module


Using Perl ”The DateCalc Module

Perl is an efficient programming language originally developed by Larry Wall. It provides a lot of functions and useful modules for solving common and complex problems. One of these modules is DateCalc by Steffen Beyer. It is an excellent supplement to PostgreSQL and provides an easy way to deal with dates and time on the application level.

An Overview

Working with dates is not always done on the database level, because when things get more complicated it might be useful to perform the operations on the application level. When retrieving data from tables, you can easily combine the process with some simple calculations, but in most cases the data returned by the database is used to perform some sort of decision-making on the application level.

The way the date and time are processed depends on the programming language you want to use for your applications. All programming languages offer highly sophisticated modules and libraries for almost all demands. This section shows how date calculations can be done with the help of Perl's DateCalc module, including working with leap years . Keep in mind that DateCalc is not the only module available to work with dates. This book explores DateCalc, because it seems highly developed, widespread, and easy to use.

Perl's DateCalc module can be found on CPAN, the worldwide archive for Perl modules. The package is currently maintained by Steffen Beyer and can be distributed under the terms of Perl itself (Artistic License or the GNU General Public License). DateCalc is based on a C library, which can be distributed under the same terms.

The Current Calendar

The first important calendar used in Europe was the Julian calendar. Initially, the Romans numbered years ab urbe condita, which means that the beginning of dates, time, and all other things was the same as the date of the founding of Rome. Today it is said that Rome was founded in 753 BC, but nobody is really 100% sure about that.

The initial Roman calendar was not adequate for an emerging empire, and after the conquest of Egypt in 48 BC Caesar consulted the Alexandrian astronomer Sosigenes about a calendar reform. The adopted calendar was identical to the Alexandrian Aristarchus' calendar of 239 BC and consisted of 12 months, 365 days, and an extra day every fourth year. In the new Roman system, January 1 was the day when the Senate took office. The Roman calendar become very widespread and was even adopted by various Christian churches .

The average length of a year in the Roman system is 365.25 days, but the "mean tropical year" is 365.2422 days long. The error accumulates over the years, and the calendar is out of sync after 131 years.

Note

The length of the mean tropical year changes slightly every year.


Pope Paul III consulted several astronomers to come up with a solution. On February 24, 1582, he issued a papal bull addressing the issue, which is now called the Gregorian Calendar reform. The most important facts for computer programmers and users of PostgreSQL have to do with leap years.

A year is a leap year if it either is divisible by 4 but not by 100 or is divisible by 400. The year 2004 is a leap year because 2004 can be divided by four but not by 100. The year 2000 was a leap year since 2000 can be divided by 400.

Knowing how the calendar works is important when dealing with dates, because you might lose or gain days if you don't know exactly what to do. Normally, you don't have to worry about it because the job is done by modules; but occasionally you might have to implement a module.

Basic Operations Using DateCalc

To simplify calculations and speed up the process of date calculations, the DateCalc module uses the Gregorian Calendar back to 1 AD. Keep this in mind, when calculating with dates from before the calendar reform.

The DateCalc module offers a huge pool of functions for almost any purpose. In this section, you learn the most important operations that can be done with DateCalc.

Sometimes it is useful to find out how many days in a certain year have passed until a certain month. These operations can be essential when dealing with leap years. DateCalc provides a function called Days_in_Year to do the job:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2001; $month=2; $days = Days_in_Year($year, $month); print "days: $days\ n"; 

This program calculates the number of days passed in 2001 until the end of February. If we execute the script, we receive one line:

 [hs@duron code]$  ./date.pl  days: 59 

February is a valid month and the program terminates successfully. Let's try the same program with instead of 2 for the month:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2001; $month=0; $days = Days_in_Year($year, $month); print "days: $days\ n"; 

As you can see in the next example, the script fails:

 [hs@duron code]$  ./date.pl  Date::Calc::Days_in_Year(): month out of range at ./date.pl line 7. 

Every time the DateCalc module faces values that are out of range, an error is displayed and the program quits. Therefore some sort of error handling has to be implemented.

Another important issue is how to include DateCalc into your program. In the previous example, we included all functions the module provides. If you need only one function of the module, it can be useful to include only the required function. In the next example, you can see how this can be done:

 #!/usr/bin/perl use Date::Calc qw(Days_in_Year); $days = Days_in_Year(2001, 2); print "days: $days\ n"; 

You can import functions explicitly by enumerating them between the parentheses of the qw() operator. If you want to include all functions, use :all instead.

Another important function when dealing with leap years is Days_in_Month :

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2000; $month=2; $days = Days_in_Month($year, $month); print "days: $days\ n"; 

The script displays days: 29 , because 2000 was a leap year and February had 29 instead of 28 days.

Not every year has 52 weeks. If you want to know the number of weeks a certain year has, a function called Weeks_in_Year can be used:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2004; $result = Weeks_in_Year($year); print "result: $result\ n"; 

You can see in the following that 2004 has 53 weeks instead of 52 weeks:

 [hs@duron code]$  ./date.pl  result: 53 

The algorithm for calculating leap years is simple. In the following example, we check whether 2004 is a leap year and whether February 29, 2004 is a valid date:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2004; $month=2; $day=29; $leap=leap_year($year); print "leap-year: $leap\ n"; $check=check_date($year, $month, $day); print "check: $check\ n"; 

leap_year returns true ( 1 ) if the specified year is a leap year, and false ( ) if it isn't. check_date works the same way:

 [hs@duron code]$  ./date.pl  leap-year: 1 check: 1 

What day of the week do we have today? This question can easily be answered by using a function called Day_of_Week :

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2001; $month=5; $day=13; print Day_of_Week($year, $month, $day)."\ n"; 

May 13, 2001 was a Sunday:

 [hs@duron code]$  ./date.pl  7 

Perl displays 7 as the result of the operation. Sunday is the seventh day of the week. In the Hebrew calendar (the one the Christian calendar is based on), the week starts with Sunday and ends with the Sabbath, which is equal to Saturday. According to Genesis, God rested from creating the world and therefore it can be seen as the last day of the week. In the Middle Ages, Catholic popes declared Sunday as the day of rest and thus the seventh day. Current standards ”such as ISO/R 2015-1971, DIN 1355, and ISO 8601 ”define Monday as the first day of the week.

If we want to know which day of the year May 14, 2001 was, we can use the following program:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2001; $month=5; $day=14; print Day_of_Year($year, $month, $day)."\ n"; 

May 14 is the 134th day of the year 2001. Operations like this might seem needless, but they can be useful when you want to compare dates easily.

Another function mainly used for comparing dates is Date_to_Days , which returns the number of days since January 1, 2001.

Note

Keep in mind that DateCalc supports only the Gregorian system.


If you want to find out which of two dates is the earlier one, you can easily do this by using Date_to_Days :

 #!/usr/bin/perl use Date::Calc qw(:all); if      (Date_to_Days(2002, 8, 9) < Date_to_Days(2003, 12, 31)) {         print "August 9th, 2002 was first\ n"; } else {         print "There seems to be a bug in the module ...\ n"; } 

The output of the program is what we expect:

 [hs@duron code]$  ./date.pl  August 9th, 2002 was first 

In Europe, the number of a weeks within a given year is often used to define the period during which certain events such as business meetings are expected to take place. This can be complicated, because if someone tells you that he wants to meet during week number 43, for instance, you will not know precisely what week that is.

Perl offers a function to find out in which week of the year a day occurs:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2003; $month=4; $day=17; print "Week number: ".Week_Number($year, $month, $day)."\ n"; 

April 17, 2001 is in week number 16 :

 [hs@duron code]$  ./date.pl  Week number: 16 

If you want to find out the date of Monday in a certain week, try the following program:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2003; $week=22; ($year, $month, $day) = Monday_of_Week($week, $year); print "$year-$month-$day\ n"; 

May 26, 2003 is the Monday of the week 22 in 2003:

 [hs@duron code]$  ./date.pl  2003-5-26 

In many cases, it's useful to calculate the difference between two date s or timestamp s. The next examples focus on how you can add or remove intervals from the date and time:

 #!/usr/bin/perl use Date::Calc qw(:all); $year1=2002; $month1=3; $day1=1; $year2=2003; $month2=3; $day2=1; $Dd = Delta_Days($year1, $month1, $day1, $year2, $month2, $day2); print "$Dd\ n"; $Dd = Delta_Days($year2, $month2, $day2, $year1, $month1, $day1); print "$Dd\ n"; 

The Delta_Days function tells us how many days there are between the first and second date passed to the function. Delta_Days can be seen as the - operator for two dates of the DateCalc module.

 [hs@duron code]$  ./date.pl  365 -365 

Delta_Days calculates date2 - date1 . Let's look at the first function call in the previous example. The second date is 365 days higher than the first one. In the second example, a negative value is displayed, because the second date is passed to the function before the first date. If both dates are equal, is returned.

Days are very often not enough. If we want to get the difference between two timestamp s, DateCalc provides the Delta_DHMS function. Assume a situation where we want to compute the difference between the result of Perl's localtime function and a timestamp . Before we get to the solution of the problem, here is an example of Perl's localtime() function :

 #!/usr/bin/perl use Date::Calc qw(:all); ($sec1,$min1,$hour1,$mday1,$mon1,$year1,$wday1,$yday1,$isdst1) = localtime(time); print "year: $year1, month: $mon1, mday: $mday1, hour: $hour1,         min: $min1, sec: $sec1\ n"; 

Let's see what happens when we execute the script:

 [hs@duron code]$  ./date.pl  year: 101, month: 4, mday: 13, hour: 16,         min: 2, sec: 46 

We executed the script on May 13, 2001. May is the fifth month, but localtime() displays 4 instead of 5 because the function starts counting with 0. 2001 is displayed as 101 , because localtime starts to count the years with 1900. To solve the problem, we add 1900 years to $year1 and 1 to $mon :

 #!/usr/bin/perl use Date::Calc qw(:all); $year2=2001; $month2=5; $day2=13; $hour2=12; $min2=25; $sec2=3; ($sec1,$min1,$hour1,$mday1,$mon1,$year1,$wday1,$yday1,$isdst1) = localtime(time); $year1+=1900; $mon1++; ($Dd, $Dh, $Dm, $Ds) = Delta_DHMS($year2, $month2, $day2, $hour2, $min2, $sec2,         $year1, $mon1, $mday1, $hour1, $min1, $sec1); print "$D1d $Dh $Dm $Ds\ n"; 

In the result, shown next, the script performs the calculations successfully and displays the right result:

 [hs@duron code]$  ./date.pl  0 3 52 59 

To avoid those nasty troubles with localtime , DateCalc provides a function called System_Clock , which returns the date and time in a format that can be used by the DateCalc module:

 ($year,$month,$day, $hour,$min,$sec, $doy,$dow,$dst) = System_Clock(); 

The difference between localtime() and the time hard-coded in the source code is 3 hours, 52 minutes, and 59 seconds.

If you think that the syntax of Delta_DHMS is a little confusing, because a lot of parameters have to be passed to the function, the result can also be achieved as shown here:

 #!/usr/bin/perl use Date::Calc qw(:all); $year2=2001; $month2=5; $day2=13; $hour2=12; $min2=25; $sec2=3; ($sec1,$min1,$hour1,$mday1,$mon1,$year1,$wday1,$yday1,$isdst1) = localtime(time); $year1+=1900; $mon1++; @time1 = ($year2, $month2, $day2, $hour2, $min2, $sec2); @time2 = ($year1, $mon1, $mday1, $hour1, $min1, $sec1); @diff=Delta_DHMS(@time1, @time2); print "@diff\ n"; 

Simply pass arrays to the functions to make the source code clearer.

Now we want to add time to a certain timestamp . For that purpose, we can use a function called Add_Delta_DHMS :

 #!/usr/bin/perl use Date::Calc qw(:all); ($sec1,$min1,$hour1,$mday1,$mon1,$year1,$wday1,$yday1,$isdst1) = localtime(time); $year1+=1900; $mon1++; @time1 = ($year1, $mon1, $mday1, $hour1, $min1, $sec1); $add_days=30; $add_hours=0; $add_min=4; $add_sec=3; @addme=($add_days, $add_hours, $add_min, $add_sec); @time2=Add_Delta_DHMS(@time1, @addme); print "before: @time1\ nafter: @time2\ n"; 

We add 30 days, 4 minutes, and 3 seconds to the current time:

 [hs@duron code]$  ./date.pl  before: 2001 5 13 16 29 33 after: 2001 6 12 16 33 36 

The operation is simple. In the previous example, we add a full timestamp to the given time. If we want to add just full days, DateCalc offers an easier method:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2000; $month=2; $day=29; $add=1; @time2=Add_Delta_Days($year, $month, $day, $add); print "@time2\ n"; 

We add 1 day to February 29, 2000.

 [hs@duron code]$  ./date.pl  2000 3 1 

The result is March 1, 2000.

What if we want to perform a subtraction instead of adding days? In the next example, we subtract 1 day from the given date:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2000; $month=2; $day=29; $add=1; @time2=Add_Delta_Days($year, $month, $day, -$add); print "@time2\ n"; 

You can see that we added a negative period of time. The result of the calculation is February 28, 2000.

In the examples, we have shown how you can add days, hours, and minutes to a certain time. Of course it is also possible to add or subtract years, months, and days. For that purpose, DateCalc offers a function called Add_Delta_YMD :

 #!/usr/bin/perl use Date::Calc qw(:all); # Input date $year=2000; $month=2; $day=29; # To be added $Dy=1; $Dm=6; $Dd=22; # Computing and printing the result @time2=Add_Delta_YMD($year, $month, $day, $Dy, $Dm, $Dd); print "@time2\ n"; 

This Perl script adds 1 year, 6 months, and 22 days to February 29, 2001; the result is shown in the following listing:

 [hs@duron code]$  ./date.pl  2001 9 22 

For logging, and many other reasons, you might want to know today's date. You have already learned about localtime and System_Clock . These two functions can be used to find out the current date, but DateCalc also offers a function to compute the date only (without current time): Today() :

 #!/usr/bin/perl use Date::Calc qw(:all); ($year, $month, $day) = Today(); print "$year-$month-$day\ n"; 

The function returns three values: one for the current year, one for the current month, and one for the day of the month:

 [hs@duron code]$  ./date.pl  2001-5-14 

Today() is used to compute the current date. If we want to find out the current time, we can use DateCalc's Now() function:

 #!/usr/bin/perl use Date::Calc qw(:all); ($hour, $min, $sec) = Now(); print "$hour-$min-$sec\ n"; 

Here is the output of the script:

 [hs@duron code]$  ./date.pl  11-54-33 

In the previous example, the current time is 11 o'clock, 54 minutes, and 33 seconds.

The current date plus the current time can be calculated in many ways. One way is to use Perl's localtime ; another way is to use DateCalc's System_Clock . Both functions return a little more information than you might need. If you want to know the current date and the current time without additional information, such as the day of the week, use the Today_and_Now function:

 #!/usr/bin/perl use Date::Calc qw(:all); ($year, $month, $day, $hour, $min, $sec) = Today_and_Now(); print "$year $month $day - $hour $min $sec\ n"; 

The result of Today_and_Now is a combination of the results of Today and Now :

 [hs@duron code]$  ./date.pl  2001 5 14 - 11 58 45 

Calculating Christian feast days can be the most difficult task when dealing with dates, especially if you are working with PostgreSQL. The DateCalc module offers a function called Easter_Sunday . Using the result of this function makes it easy to find out the dates of the most important Christian feast days related to Easter. The following table is an overview of all important dates and their offset to Easter Sunday:

Feast Day Offset
Carnival Monday - “48 days
Mardi Gras - “47 days
Ash Wednesday - “46 days
Palm Sunday - “7 days
Easter Friday - “2 days
Easter Saturday - “1 day
Easter Monday +1 day
Ascension of Christ +39 days
Whitsunday +49 days
Whitmonday +50 days
Feast of Corpus Christi +60 days

If you want to find out the date of Whitmonday in 2002, use the following script:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2002; ($year, $month, $day) = Add_Delta_Days(Easter_Sunday($year), 50); print "$year-$month-$day\ n"; 

Whitmonday is 50 days after Easter Sunday and therefore the result is the following:

 [hs@duron code]$  ./date.pl  2002-5-20 

Dates are not always defined as a number. Sometimes you will face the situation in which you have to convert a string into a number. In the following example, we try to convert September and Wednesday to numbers that we can use for further calculations:

 #!/usr/bin/perl use Date::Calc qw(:all); $month="September"; $day="Wednesday"; print Decode_Month($month)." - ".Decode_Day_of_Week($day)."\ n"; 

Because September is the ninth month of the year and Wednesday is the third day of the week, the output shown here is not surprising:

 [hs@duron code]$  ./date.pl  9 - 3 

Both functions in the script are highly language-dependent. To find out which language you have selected, try the following piece of code:

 #!/usr/bin/perl use Date::Calc qw(:all); print Language."\ n"; 

By default, the language is set to 1 , which is English. The core distribution of the DateCalc module supports seven languages (in version 4.3). If you need the complete list of all supported languages, check out the man pages of the module (use man Date::Calc ), because the number of languages supported by the module is constantly increasing.

Until now, we have always displayed the date as a number. This might not be suitable for your applications, because in most cases it is more comfortable for the user to display the date in a formatted way. In general, two functions are provided:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2002; $month=4; $day=26; print Date_to_Text($year, $month, $day)."\ n"; print Date_to_Text_Long($year, $month, $day)."\ n"; 

Let's execute the script:

 [hs@duron code]$  ./date.pl  Fri 26-Apr-2002 Friday, April 26th 2002 

In the output of the script, you can see the difference between the two functions.

Sometimes you might want to see the calendar of a given month. DateCalc provides a function called Calendar to do the job:

 #!/usr/bin/perl use Date::Calc qw(:all); $year=2002; $month=4; $result = Calendar($year, $month); print $result; 

Calendar returns a properly formatted calendar. We can use the result of the function directly:

 [hs@duron code]$  ./date.pl  April 2002 Mon Tue Wed Thu Fri Sat Sun   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 

Steffen Beyer's DateCalc module is one of the most powerful pieces of software currently available for Perl. The next section explores how you can build wonderful applications with PostgreSQL, Perl, and DateCalc.

Using DateCalc with PostgreSQL

In this section, we build small database-enabled applications to show you practical solutions with PostgreSQL. First we create a table called timefield , where we will try to insert some data with the help of a Perl script:

  CREATE TABLE mydates(timefield timestamp);  

The data that you have to insert into a database is often produced by an application. In this case, you need reliable tools to interact with the database. With PostgreSQL and Perl, the DBI module is the most widespread module available; therefore, we will use it in this section.

In general, the main problem when working with applications, modules, and databases is that the format of the data used by the various components is not always the same. Many standards for displaying data have been developed in the past, but for technical reasons interaction is not always as simple as it should be. In this section, you learn about converting data.

Another important decision programmers have to make is which operations will be performed on which level. Simple calculations can be performed by either the database or the application. It depends on you and what you think is better for your application.

Let's get to our first example. We start with a simple program that inserts today's date into the database. An SQL command is created and the data is inserted into the database as timestamp :

 #!/usr/bin/perl use DBI; use Date::Calc qw(:all); $dbh=DBI->connect("dbi:Pg:dbname=datecalc; port=5432", "hs", "anypasswd") or         die "can't connect: $!\ n"; @time=Today(); $date=Date_to_Text_Long(@time); $sql="INSERT INTO mydates VALUES('$date'::timestamp)"; print "$sql\ n"; $ret=$dbh->do($sql) or         die "cannot execute ($sql) because of $!\ n"; 

After connecting to the database, we compute today's date by using the Today() function. Then we convert the date to a string. In this case, we have to use Date_to_Text_Long , because the output of Date_to_Text would lead to a syntax error in the SQL statement.

If we execute the script, we see the SQL statement passed to PostgreSQL:

 [hs@duron code]$  ./pg_date.pl  INSERT INTO mydates VALUES('Monday, May 14th 2001'::timestamp) 

No error has been displayed, so the record has successfully been inserted into the database:

 datecalc=#  SELECT * FROM mydates;  timefield ------------------------  2001-05-14 00:00:00+02 (1 row) 

PostgreSQL automatically uses the default time zone. You have to take care of this when working with DateCalc, because time zones are (at the time we wrote this book) not supported by the Perl module.

In the next example, we use a full timestamp , including date and time:

 #!/usr/bin/perl use DBI; use Date::Calc qw(:all); $dbh=DBI->connect("dbi:Pg:dbname=datecalc; port=5432", "hs", "anypasswd") or         die "can't connect: $!\ n"; $date=Date_to_Text_Long(Today()); @now=Now(); $sql="INSERT INTO mydates VALUES('$date @now[0]:@now[1]:@now[2]+02'::timestamp)"; print "$sql\ n"; $ret=$dbh->do($sql) or         die "cannot execute ($sql) because of $!\ n"; 

In the next SQL statement, we added the result of the Now() function and the time zone to the SQL statement.

  INSERT INTO mydates VALUES('Monday, May 14th 2001 14:59:30+02'::timestamp)  

The statement is inserted into the table properly:

 datecalc=#  SELECT * FROM mydates;  timefield ------------------------  2001-05-14 14:59:30+02 (1 row) 

Inserting data is not a difficult task. In most cases, you can write a few lines of Perl code to do all the conversions that you need to fit PostgreSQL's demands.

Let's look at some SELECT statements now. The following shows how data can be read from the result of a query:

 #!/usr/bin/perl use DBI; use Date::Calc qw(:all); $dbh=DBI->connect("dbi:Pg:dbname=datecalc; port=5432", "hs", "anypasswd") or         die "can't connect: $!\ n"; $sql="SELECT timefield FROM mydates"; @row=$dbh->selectrow_array($sql); ($year, $month, $day, $hour, $min, $sec, $zone) = postgres2perl(@row[0]); print "Result: $year $month $day $hour $min $sec $zone\ n"; sub postgres2perl {         return(substr($_[0],0,4),                 substr($_[0],5,2),                 substr($_[0],9,2),                 substr($_[0],11,2),                 substr($_[0],14,2),                 substr($_[0],17,2),                 substr($_[0],20,3)); } 

We select the only record in the table and store it in @row . The first field contains the timestamp , and we pass the value to a function called postgres2perl that we define next. This function performs simple substring operations and returns the result in an array. The result of the function is finally displayed by a simple print :

 [hs@duron code]$  ./pg_date.pl  Result: 2001 05 4  14 59 30 02 

We have even extracted the time zone from the field. Now we can use the data the way it can be done with the DateCalc module.

The situation is far more complex when working with intervals, because intervals do not consist of fixed-length components. Depending on the data returned, the structure of the output might vary significantly. The following is a good example:

 datecalc=#  SELECT '1 century, 2 decades, 1 month, 1 week, 3 days, 2 hours, 1   minute, 2 sec'::interval;  ?column? ----------------------------------  120 years 1 mon 10 days 02:01:02 (1 row) 

The database converts centuries into years and weeks into days, but how does the output look if we omit some components?

 datecalc=#  SELECT '1 day, 2 hours, 1 minute, 2 sec'::interval;  ?column? ----------------  1 day 02:01:02 (1 row) 

The number of days is now labeled day instead of days as it was before. PostgreSQL displays the singular of the word correctly. No information about the amount of years or months is displayed, because both values are . Next we present a prototype of a script, which converts input data using interval as the datatype to a format we can use with the DateCalc module:

 #!/usr/bin/perl use DBI; use Date::Calc qw(:all); $dbh=DBI->connect("dbi:Pg:dbname=datecalc; port=5432", "hs", "anypasswd") or         die "can't connect: $!\ n"; $sql="SELECT '2 year, 1 minute, 2 sec'::interval"; @row=$dbh->selectrow_array($sql); ($year, $mon, $day, $hour, $min, $sec)=interval2perl(@row[0]); print "years: $year, months: $mon, days: $day, hours: $hour, "; print "min: $min, sec: $sec\ n"; # Converting intervals to variables sub interval2perl {      my $i, @array, $year, $years, $mon, $mons;      my $day, $days, $hour, $min, $sec;      my @array=split(/\  +/, $_[0]);      if      (@array[1]) {  ${ @array[1]} =@array[0]; }      if      (@array[3]) {  ${ @array[3]} =@array[2]; }      if      (@array[5]) {  ${ @array[5]} =@array[4]; }      foreach $i (0, 2, 4, 6)      {              if     (@array[$i] =~ /:/)              {                     if      (length @array[$i] eq 8)              {                             ($hour, $min, $sec) = split(/:/, @array[$i]);              }              else              {                             ($hour, $min) = split(/:/, @array[$i]);              }              }      }      return($year+$years, $mon+$mons, $day+$days, $hour, $min, $sec); } 

As promised , the script is more complex than the one we used to convert timestamp . After executing the SQL code in $sql , we call interval2perl with the field that we want to convert. At the beginning of the function, we define some local variables that we will need later in the script. In the next step, we split the input data with the help of a simple regular expression. The pattern we use for the split matches one or more blanks in the string. The if statements check whether certain fields are defined. The array created by the split command might contain up to 7 fields ( 6 ). Strings, which define the type of the value in the previous field, can be found in position 1, 3, or 5. If field 5, for instance, is defined, we assign the value of the field before to a variable that has the name of the field we are currently accessing. If, for instance, field 1 contains the string years , the value in field 0 is assigned to a variable called $years . This can be done easily, because Perl supports dynamic variables. Now that we have extracted and processed the strings, we can transform hours, minutes, and seconds. These three values can be defined in position 0, 2, 4, or 6 in the string and can easily be found, because they are separated by a colon .

To make things a little bit more complicated, PostgreSQL does not display the amount of seconds if it is . If seconds are defined, the string is 8 characters long. We have to take this into consideration when splitting the field.

Finally we return the values we have extracted, and now there is only one hurdle left. Some strings might contain singular or plural words, depending on the data in the previous field. We add the variable containing the data of the singular words and the variable containing the data of the plural word. One of the two is always undefined and therefore treated as 0 by Perl.

If we execute the script, we receive the following result:

 [hs@duron code]$  ./pg_date.pl  years: 2, months: 0, days: 0, hours: 00, min: 01, sec: 02 

The program has extracted all necessary information from the string and displayed it on the screen.

Converting the date and time used by DateCalc to PostgreSQL is rather easy. The following script shows how it can be done:

 #!/usr/bin/perl use DBI; use Date::Calc qw(:all); $year=2; $month=3; $day=32; $hour=9; $min=56; $sec=32; $interval=&perl2interval($year, $month, $day, $hour, $min, $sec); print "Interval: $interval\ n"; # Converting intervals to variables sub perl2interval {         my $interval="$year years, $month months, $day days, $hour hours, ";         $interval.="$min minutes, $sec seconds";         return($interval); } 

The script produces a valid interval , which can be cast and used by PostgreSQL directly:

 [hs@duron code]$  ./pg_date.pl  Interval: 2 years, 3 months, 32 days, 9 hours, 56 minutes, 32 seconds 

We do not have to care about singular and plural words, because PostgreSQL understands both. In the following example, you can see that PostgreSQL does not make any difference in how the data is passed to the database but displays the output correctly:

 datecalc=#  SELECT '2 year, 3 months, 32 days, 1 hours, 0 minutes, 1 seconds'::interval;  ?column? ---------------------------------  2 years 3 mons 32 days 01:00:01 (1 row) 

In general, Perl, PostgreSQL, and DateCalc make a good team. If you want to find out more, check out the man pages of DateCalc.



PostgreSQL Developer's Handbook2001
PostgreSQL Developer's Handbook2001
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net