Select near date

andreasft · #1 (**permalink**) 09-03-10, 07:20

Hi all.

I have a geographic dataset using quadrat grid cells for each year. Each cell has a unique identifier. In some cases two countries might exist within these cell during the same year. What i want to do is to keep the entry which existed at 1st of January within the year.

The data has one cellid, a country code, a startyear, startmonth and startday column, in addition to endyear, endmonth and endday columns.

In basic, I want to select the country within the cell which existed january 1 in a year.

For instance, here i want to select the one of the two entries which existed january 1st 1948.

Any ideas?

divider gwcode gwsyear gwsmonth gwsday gweyear gwemonth gweday
199612 20 1946 1 1 1948 6 30
199612 20 1948 7 1 2008 6 30

futurity · #2 (**permalink**) 09-06-10, 13:20

I'm not sure why elected to not use a DATE data type, but regardless ....

Does this not work?

Code:

select *
from mytable
where (month, day) = (1, 1)

loquin · #3 (**permalink**) 09-07-10, 16:03

He has a start 'date' and an end 'date', futurity. In his sample data, the first record has a start date of JAN 1, 1946 and an end date of JUN 30 1948... Which should return a 'hit' for years 1946, 1947, and 1948.

If using date types (rather than the separate month day and year fields,) the query would look something like

Code:

SELECT * 
FROM YourTable 
WHERE start_date <= '1-1-1947' AND end_date >= '1-1-1947'

Since you're using separate month, day, and year fields for both start and end dates, you'll need to concatenate the fields to form the string representation of a valid date, then cast the string to a date for the comparison.

Code:

SELECT * 
FROM YourTable 
WHERE start_month || '-' || start_day || '-' || start_year :: date <= '1-1-1947' 
   AND end_month || '-' || end_day || '-' || end_year :: date <= '1-1-1947'

(Sure would be easier if you use dates, hmm?)

artacus72 · #4 (**permalink**) 09-07-10, 16:17

Man talk about making your life needlessly more complicated. This reminds me of a schema design from back in the days of flat file databases where the "database" had no concept of a date data type. But if you have the option to alter your schema and use the date data type, by all means do it. Otherwise, you can make your life a little easier (performance will still suck) by creating a view that casts into a date.

futurity · #5 (**permalink**) 09-08-10, 10:55

Quote:

Originally Posted by loquin

He has a start 'date' and an end 'date', futurity.

Oops. Well, in that case, I'd advocate even more strongly changing the schema to a couple of DATE columns.

Quote:

Code:

SELECT * 
FROM YourTable 
WHERE start_date <= '1-1-1947' AND end_date >= '1-1-1947'

For what it's worth, you could also use BETWEEN:

Code:

SELECT * 
FROM YourTable 
WHERE '1-1-1947' BETWEEN start_date AND end_date

Yeah, functionally it's the exact same thing. But seeing as the developers were nice enough to put it in and all ....

loquin · #6 (**permalink**) 09-08-10, 17:00

I hadn't tested the equivalency of between though...

specifically, is it equivalent to

Code:

'1-1-1947' >= start_date and '1-1-1947' <= end_date
-- or
'1-1-1947' > start_date and '1-1-1947' < end_date
-- or 
'1-1-1947' >= start_date and '1-1-1947' < end_date
-- or
'1-1-1947' > start_date and '1-1-1947' <= end_date

Edit ... check the documentation, Lou!

Quote:

Originally Posted by documenation

a BETWEEN x AND y

is equivalent to

a >= x AND a <= y

Internet Services

Web Development

Digital Marketing

Consumer Tech