date queries with portions of the date unknown

[bps4484]

I'm trying to do some date queries on a table, and I want to know if there is some builtin variable within the date datatype which can represent that a day, month, or year could be any value.

For instance, if the variable were "*", I could write:

table.datefield >= '*-10-*'

and any date in table.datefield which had the month 10, 11 or 12 would pass the predicate.

I know there are functions which can take out just the day, month, or year, but I'm dealing with an application where any part of the date may be left variable, or none of the parts of the date could be variable, so parsing in this way doesn't exactly work without writing a lot of code to build the sql for each specific case (nothing variable, only the day variable, only the month, day and month, ect. I don't want to code all of that if I don't have to)

Is there a symbol like this which I can insert into a date query which has this functionality?

Thanks.

[michaelb]

I am not sure I understand what you have to deal with... perhaps few examples of the actual values you have on this column would help.

If you can afford the real long-term solution you should consider changing the application in a way that all dates are stored in the database in the same format. The existing values should be converted to this format as well.
If you go this route avoid storing dates in a varchar type, use one of the native Postgres date/time datatypes.

[bps4484]

sorry I wasn't clear, I do have this date field stored as a date datatype.

The problem isn't with how the dates are stored, the problem is with the queries predicates I use to select out the tuples.

Say I wanted any dates that were from october to december, meaning that a date could be on any day, or any year, but as long as it was in the months of october, november, or december it passes the predicate. If there were a variable like I described, you could say:

table.datefield >= '*-10-*' AND table.datefield <= '*-12-*'

In another example, if I wanted dates from october 2006 to febuary 2006, I could just say:

table.datefield >= '2005-10-*' AND table.datefield <= '2006-2-*'

The first example can be solved by just parsing the year, month, and day out of the date using the date_part() function and doing queries on those peices individually, but that strategy wouldn't work in the second case because a date like '2006-1-5' wouldn't pass date_part('month', table.datefield) >= 10, but I want that date to pass my predicates.

I'm still not sure if I phrased this correctly, but hopefully these examples explained what I am looking for better.

[michaelb]

Thanks for clarification!

As you already noted the first case can make use of the date_part function:

SELECT field-list FROM tabname WHERE date_part('month', column-name) BETWEEN 10 and 12;
 

As far as your second case, with the date datatype you should not have any values like '2006-1-5'.
Even if this is what you use with INSERT, the actual value on the table should be '2006-01-05', so the date_part function should have no troubles parsing this value.

You can also try to modify the example you posted above:

table.datefield >= '2005-10-*' AND table.datefield <= '2006-2-*'
 -- change it to 
table.datefield >= '2005-10-01' AND table.datefield <= '2006-02-28'
 

(BETWEEN may come handy here too)

One catch here is that every month begins with 01, but not all of them have the same end-date (think of 28, 29, 30, 31),
and if you give a non-existant end-date the query may error out, so this approach may require little extra thought.

There could be more tricks to employ here, but first let us know whether this info was helpful.

[bps4484]

Your right, I could fill in the first day of the month and last day of the month when the days of a query aren't specified.

As you said though, it does require some more thought because of the different days that months end with. maybe I'll just make a map for that, but I was wondering if there was something like this "*" that I described to use as a taking any value. This would make my code creating the sql, and the sql, a lot more clean.

As this post has been up for a few days though with no response, I'm probably going to guess that this doesn't exist and go with the way you propose.

Thanks for your help.

[michaelb]

I did not mean to discourage you. Let me put it this way: I am sure, that there are other methods to accomplish what you need, but quite often I don't have time to explore every possible approach or technique.
So if by any reason what we had so far isn't helpful, there will be more to discuss and try out... meaning do come back to this thread if you don't like the last suggested approach.

By the way, I'm positive there are ways of finding the last day of the month without keeping a hardcoded matrix.