Business Days / Working Days SQL for Postgres

December 2, 2009 by

We use Postgres 8.4 as our database and I’m really impressed with how it performs and the amount of features it has.

Recently we’ve needed some functions for working with dates and business days, so I looked for some help on this.

I found a good post here: http://archives.postgresql.org/pgsql-sql/2009-11/msg00038.php

In the process, I made a few changes, including the use of Postgres 8.4 ‘with’ syntax, which I find really useful in making things understandable. Obviously, this needs Postgres 8.4 to work.

Here are the results, in case anyone out there is looking for similar functions (any comments / suggestions / improvements welcome):

Holiday Table
First, create a table containing all the holidays that aren’t working days:

CREATE TABLE holidays (
   id serial,
   description character varying (50) not null,
   holiday date not NULL
)
WITHOUT OIDS
TABLESPACE pg_default;

insert into holidays (description, holiday) values ('Christmas Day', '2009-12-25');
insert into holidays (description, holiday) values ('Boxing Day', '2009-12-28');
insert into holidays (description, holiday) values ('New Years Day', '2010-01-01');
insert into holidays (description, holiday) values ('Good Friday', '2010-04-02');
insert into holidays (description, holiday) values ('Easter Monday', '2010-04-05');

Add Working Days to a Date
Now, create a function to add working days to a given date:
NOTE: If you input a non-working day and add 0 days to it, you will get no result!

CREATE OR REPLACE FUNCTION addbusinessdays(date, integer)
  RETURNS date AS
$BODY$
with alldates as (
	SELECT i,
	-- Check for negatives
	$1 + (i * case when $2 < 0 then -1 else 1 end) AS date
	-- NOTE we add 5 and x 2, to make sure the sequence has enough in it to cope with weekends/hols that get omitted later
	FROM generate_series(0,(abs($2) + 5)*2) i
),
days as (
	select i, date, extract('dow' from date) as dow
	from alldates
),
businessdays as (
	select i, date, d.dow from days d
	left join holidays h on d.date=h.holiday
	where h.holiday is null
	and d.dow between 1 and 5
	order by i
)
--select count(*) from businessdays where date between '2010-04-01' and '2010-04-07';
-- now to add biz days to a date
select date from businessdays where
        case when $2 > 0 then date >=$1 when $2 < 0 then date <=$1 else date =$1 end
	limit 1
	offset abs($2)
$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;
ALTER FUNCTION addbusinessdays(date, integer) OWNER TO postgres;

select * from addbusinessdays('2009-12-24', 1)

Finally to get the number of working days between two dates:

CREATE OR REPLACE FUNCTION countbusinessdays(date, date)
  RETURNS bigint AS
$BODY$
with alldates as (
	SELECT i, $1 + i AS date, extract ('dow'  from $1::date + i) AS dow
	FROM generate_series(0,$2-$1) i
),
businessdays as (
	select date, dow from alldates dt
	left join holidays h on dt.date=h.holiday
	where h.holiday is null
	and dow between 1 and 5
	order by date
)
select greatest(0,count(*)-1) from businessdays where date between $1 and $2;

$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;
ALTER FUNCTION countbusinessdays(date, date) OWNER TO postgres;

select * from countbusinessdays('2009-12-24','2009-12-29');

That’s it! Hope it’s useful to someone.
Bob

Advertisements

Open Source Business Models

June 29, 2009 by

The vast majority of the systems we run are Open Source – for a simple reason; we are a small company and we want to produce enterprise grade solutions at a fraction of the price that a serious Enterprise would pay. We simply can’t afford to pay the amounts that the Enterprises pay. Not until we are making considerably more money than we are currently.

Having said that however, we are responsible citizens  and would like to contribute back where we can. Generally, that is either in code contributions or by paying for the software that we use. Code contributions are difficult, we don’t have a lot of time to spend on making changes to the software that we use. We also use far more than would be practical for us to be across. So we pick one or two systems that we really need more from and contribute to those.

For the rest, we would love to be able to pay reasonable amounts of money for them. The issue is that invariably the pricing structure is either free or enterprise grade (i.e. >= $10k per CPU per year) style pricing. This includes support, escalations etc. The trouble is that this leaves us with no option to really pay anything.  Personally I would have thought there would be thousands more companies like us than there are major enterprises willing to pay. I would like to see products costed at $1k per year for access to the product (via RPM) and security updates. I don’t need the support (not at that price anyway).

The products that do provide that level of pricing, I pay for, the rest, I use for free. While I feel guilty about that, until the business models of the companies providing these systems change, there is not much else I can do.

Cheers,

David

Backups & SAN Storage

January 27, 2009 by

Enterprises typically spend huge dollars on their storage solutions (which admittedly are excellent). But if you don’t have several hundred thousand dollars, what do you do? Run CentOS!

We are putting together our backup solution – we use Bacula to manage our backups which is an excellent network aware solution that seems rock solid for us. We needed to compile our own RPMs from the source, but this was a simple matter of running:

# rpmbuild --rebuild --define "build_centos5 1" --define "build_postgresql 1" \
                     --define 'build_gconsole 1'--define 'build_bat 1' \
                     bacula-<version>.src.rpm

which we then imported into our yum repository. The configuration for this was pretty complicated the first time we set it up, but with the excellent HOWTOs on the site was not too much pain. We then had to decide where to store all the data. Tape drives are expensive, and slow and was not something we really wanted to do if we didn’t have to, so we built our own SAN. A SAN (Storage Attached Network) is a device which looks like a normal disk device on the computer, but in fact sends all the data across the network to a storage device.

Building a SAN is as simple as installing correct package and configuring it up:

# yum install scsi-target-utils
# chkconfig tgtd on
# service tgtd start
# /usr/sbin/tgtadm --lld iscsi --op new  --mode target --tid 1 \
                   -T iqn.2009-01.example.com.san1:storage.backup
# /usr/sbin/tgtadm --lld iscsi --op new  --mode logicalunit --tid 1 --lun 1 \
                   -b /dev/VolGroup00/Backup
# /usr/sbin/tgtadm --lld iscsi --op bind --mode target --tid 1 -I ALL
# echo >> /etc/rc.local <<EOF
/usr/sbin/tgtadm --lld iscsi --op new  --mode target --tid 1 \
                 -T iqn.2009-01.example.com.san1:storage.backup
/usr/sbin/tgtadm --lld iscsi --op new  --mode logicalunit --tid 1 --lun 1 \
                 -b /dev/VolGroup00/Backup
/usr/sbin/tgtadm --lld iscsi --op bind --mode target --tid 1 -I ALL
EOF

and you are done. Then you need to bind to that storage on the server that requires access to it. This is as simple as:

# yum install iscsi-initiator-utils
# echo "InitiatorName=iqn.2008-11.example.com.`hostname`" \
  > /etc/iscsi/initiatorname.iscsi
# iscsiadm -m discovery -t st -p <san address>
# iscsiadm --mode node --targetname  iqn.2009-01.example.com.san1:storage.backup \
           --portal <san address>:3260 --login
# mount /dev/<new device> /mnt

You now have a working SAN hooked up to the machine that requires it. Couple this with drbd and you have a highly available storage setup which will run as fast as your network (Gig-E is obviously recommended for this). Using a distributed filesystem (such as GFS2) should allow many machines to access the same data simultaneously. However, we have not done this as yet.