[koolkao]

I have found SQL to be cumbersome for expressing temporal relationships, eg find all the event As that happen within one week of event B. There's not necessarily a data schema link between the table for event A and the table for event B.

how does htsql do with this?

[clarkevans]

Let's say you wish to list all students in a university, and then, show all other students with the same birth year and month. So, you'd start with ``/student`` and then you could select all columns using the asterisk. Next, you could use the ``fork()`` operation to join to the same table based on a set of columns that are correlated.

/student{, /fork(year(dob),month(dob))}

http://demo.htsql.org/student%7B*,%20/fork%28year%28dob%29,m...

You could wrap this up in a definition to make it a bit easier to understand:

/student .define(students_same_month := fork(year(dob),month(dob))) .select(, /students_same_month )

http://demo.htsql.org/student%0A.define%28students_same_mont...

With the more general case, let's say you're interested in listing per semester, which students started during that semester. In this case, you start with ``/semester`` and then define the correlated set, ``starting_students`` which uses the ``@`` attachment operator to link the two sets. Then, you'd select columns from semester and the list of correlated starting students.

/semester .define(starting_students:= (student.start_date>=begin_date& student.start_date<=end_date)@student) {, /starting_students }

http://demo.htsql.org/semester.define%28starting_students:=%...

Typically, you'd include the new link definition, ``starting_students`` in your configuration file so that you don't have to define it again... then the query is quite intuitive:

/semester{, /starting_students }

While this all may seem complex, it's not an easy problem. More importantly, HTSQL has the notion of "navigation". You're not filtering a cross product of semesters and students, instead, you're defining a named linkage from each semester to a set of students. The rows returned in the top level of query are semesters and the rows returned in the nested level are students.

[koolkao]

Thank you for the examples.

I made some queries that are analogous to my temporal query needs

Here I'm looking for every student, other students with DOB within 2 weeks of the given student: http://demo.htsql.org/student.define(similar_students:=%20(s...

Here for every semester with at least one student, I find the oldest and the youngest students enrolled: http://demo.htsql.org/semester%20.define(starting_students:=...

No being a computer scientist I have to admit I do not appreciate the intricacies of the 'problems with SQL' blog entries. But working with htsql I gotta say it seems a lot more intuitive than SQL. It feels like the logic correspond much better to my mental model. And that there is much less of the jumping up and down the code to nest my SQL code logic that I find myself doing all the time.

Is there a way to install this on a PostgresQL instance on Win8?

[clarkevans]

HTSQL requires Python 2.7 and psycopg2 binary. As long as those install, I don't see any problems. We have tested it with older versions of Windows without any issues. If you run into a problem, you could report an issue: https://bitbucket.org/prometheus/htsql/issues