Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    1

    Unanswered: Select near date

    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

  2. #2
    Join Date
    May 2008
    Posts
    277
    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)

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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?)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    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.

  5. #5
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by loquin View Post
    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.

    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 ....

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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
    Last edited by loquin; 09-08-10 at 18:06.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •