Scheduling with Date Mod Part 2

20 10 2009

So as the title suggests, this is the second part of a series on scheduling. In the first part we did some basic setup. Now we are ready to dig in. The goal here is to be able to model any recurring pattern no matter the cycle.

The linchpin in this solution is to define an anchor date and the cycle length for every schedule. Given those two pieces of information, we can calculate the day of cycle for any given date using date mod. So lets create a couple of tables.
Read the rest of this entry »


Scheduling with Date Mod Part 1

19 10 2009

One question that seems to come up a lot is how to model recurring schedules. That’s understandable, because schedules can get out of hand pretty quickly.

We need a template so that we can define a schedule and then some how project it out on a repeating basis. The single week schedules are pretty easy. We just record the day of week and for each schedule and day we look up that day of week to get the shift for that day.
Read the rest of this entry »

Thinking in Sets of Sets

6 10 2009

Over the summer, I built the Chronos temporal toolkit for Oracle. And for the last while I’ve been porting it to Postgres and working on the documentation.

The SQL standard defines several data types to model instances of times. But rarely does anything happen instantaneously. Often what we need to model is a period of time; for instance an airline flight, a hotel stay, a bus route, an employee’s shift, a doctor’s appointment, a leave request. And yet there is no standard defined to handle this. So instead we use some combination of start and end time, an anchor time and interval, or an anchor time and a number representing seconds, minutes or days. Read the rest of this entry »

Internal Data Representation

3 10 2009

Have you ever wondered how your database represents the timestamp you just entered or how many bytes it takes to store a boolean or interval?

Oracle has a handy dump function that lets us peer into the internal representation of our data. Dump takes any type of input and returns a varchar2 describing the data type, length and the internal representation. Output might look like this:
‘Typ=96 Len=4: 84,101,99,104’
Read the rest of this entry »

Porting Postgres favorites to Oracle

30 07 2009

For the last month or so, I have been creating temporal extensions for both Postgres and Oracle. Of the two, the Postgres version was easier to implement because the array functionality in Postgres is the best of any dbms out there. So for other developers who split their time between Postgres and Oracle, here is how to create the equivalent of array_agg(), unnest() and generate_series() in Oracle.
Read the rest of this entry »

Simplify XML Parsing in Postgres

19 06 2009

I was reading this article over at Postgres Online Journal about processing GPX XML files and it reminded me how much cruft is involved with extracting values from XML in Postgres.

The XPath function is great for some things, especially when you want to return an array of XML nodes. But it ALWAYS returns an XML array. If you want to iterate over a chunk of XML, for example pulling all of the waypoint nodes, then XPath is the perfect tool for the job. But if you want to extract a single value, it becomes clunky. So if you want to get the latitude for a node, you’ve got to get the 1st element in the XML array, cast it as varchar in order to cast it again to numeric. It is not exactly straight forward. And if you spend a lot of time parsing XML files, that cruft can really add up.

So we are going to pull a(nother) page from Oracle’s book and create a few functions that will make it easier to work with XML in Postgres.  The first thing we need is a way to extract a single value from somewhere in an XML node. Oracle’s extractvalue() function does precisely this. Here’s what it looks like in Postgres: Read the rest of this entry »

Web Developer’s Date/Time Cheatsheet

2 06 2009

If you work in more than one database or  programming language, it is almost impossible to keep all of the different date/time formats straight. As a developer, I found myself going back to the manuals for date/time formats more than anything else.

Here’s a web developer’s date/time cheatsheet. It covers the Oracle, Postgres, MySQL and MS SQL databases; and Java, PHP, Python, Ruby and Flex/ActionScript3 programming languages.