1 Topic by FionaCEDaR

  • FionaCEDaR
  • Administrator
  • Offline
  • From: CEDaR, Northern Ireland
  • Registered: 16-09-2009
  • Posts: 165

Topic: Importing error into warehouse1

Hi
I'm importing a lot of existing records from our Recorder 6 database into the BRC warehouse1. I have been successful with the majority of records - even ones with vague dates and date ranges that I wasn't sure would work.

However, I'm having a problem with two sets of vague dates in mmm-yy format:

Feb-97 (and all other years) I get the following error: There was an SQL error: ERROR:  date/time field value out of range: "1997-02-29"
LINE 1: ...sref", "date_start", "date_type", "geom") VALUES ('1997-02-2...
                                                             ^ - INSERT INTO "samples" ("date_end", "location_name", "survey_id", "recorder_names", "created_on", "created_by_id", "updated_on", "updated_by_id", "entered_sref_system", "entered_sref", "date_start", "date_type", "geom") VALUES ('1997-02-29', 'Pubble Forest', 53, NULL, '20121102 15:16:18', 1, '20121102 15:16:18', 1, 'osie', 'H3346', '1997-02-01', 'O', '010300002031BF0D000100000005000000F45C1E0CA47429C1A00FA051479D5B416C8028668B7429C1112EAA6CF49E5B41CBA794052A6729C1A724E949F19E5B41BBC3DC6A436729C1518D4F2F449D5B41F45C1E0CA47429C1A00FA051479D5B41')

Oct-92 (and all other years) I get the following error: Please supply a date for your observation.

The weirdest thing is that the system accepted all other months in this format. I just can't figure out what is going wrong.

Any ideas?

Many thanks,
Fiona

Fiona McCrory
CEDaR Website Officer
www.nmni.com/cedar

FionaCEDaR's Website

2 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Importing error into warehouse1

Hi Fiona,

John is on holiday this week and I have already left him one bug report about vague date processing. This looks like it is another one.

In the first error I observe that, from Feb-97, an end date of 29/02/1997 is being calculated by PHP and, of course, that date is not valid as it was not a leap year, which I think the database realises.

With regard to the October date, I'm not sure what is going on. I'm just looking at http://code.google.com/p/indicia/source … e_date.php for the first time and see a function singleMonthInYearFormats() which looks like it defines the acceptable date formats. I would have expected, looking at this, that there should be no hyphen betwen month and year.

Jim Bacon.

3 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Importing error into warehouse1

Hi Fiona/Jim
Indeed, there is a bug here regarding the Feb-97 date. I've now fixed this.

Jim, please can you put revision 4925 onto the warehouses.

Jim is also right, the expected format should be Oct 92, which probably explains the problems you had with this date.

Best wishes

John van Breda
Biodiverse IT

johnvanbreda's Website

4 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Importing error into warehouse1

Hi

Revision applied.
Try your February records again, Fiona.

Jim Bacon.

5 Reply by FionaCEDaR

  • FionaCEDaR
  • Administrator
  • Offline
  • From: CEDaR, Northern Ireland
  • Registered: 16-09-2009
  • Posts: 165

Re: Importing error into warehouse1

Hi Jim and John,

Right... I have eventually got all the records to import but I had to do some fiddling around with date formats.

For the October records I tried a date range i.e. 01/10/96-31/10/96 but this didn't work. All were rejected. The error through this was "Please supply a date for your observation."

I then reverted them back to mmm-yy and changed the format to a custom format in Excel mmm yy

All except three records were then accepted on upload. The three rejected were Oct-03, Oct-06 and Oct-08 (they were the only records I had in Oct with a zero number year).

I moved onto the February records and all were rejected until I did the custom format of mmm yy and then one was rejected. This was again the only zero number year record I had)

So I now had 4 records still to import. I changed to the custom format mmm yyyy and all 4 records were accepted. Phew!

The map on our squirrel site now looks pretty good with over 2200 records in the warehouse - http://www.habitas.org.uk/squirrels/distribution-map

Many thanks for all your help and I hope some of this is useful.

Fiona

Fiona McCrory
CEDaR Website Officer
www.nmni.com/cedar

FionaCEDaR's Website

6 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Importing error into warehouse1

Hi

Thanks for the feed back, Fiona. It sounds incorrect that the dates with a zero in the year were rejected but everything else you describe sounds like designed behaviour.

To save others the trouble of following the link to the code, here are the acceptable vague-date month-in-year formats. Listed is the PHP format string, followed by an example.

%Y-%m   1998-06
%m/%Y   06/1998
%m/%y   06/98
%B %Y   June 1998
%b %Y   Jun 1998
%B %y   June 98
%b %y   Jun 98

Jim Bacon

7 Reply by FionaCEDaR

  • FionaCEDaR
  • Administrator
  • Offline
  • From: CEDaR, Northern Ireland
  • Registered: 16-09-2009
  • Posts: 165

Re: Importing error into warehouse1

Hi Jim,
I thought I should mention that all other months (in mmm-yy format) apart from Feb and Oct uploaded without a hitch so, for example, "Jun-06" uploaded fine. I found that Excel had a great reluctance to accept the removal of the "-" in the format. It often reverted it! Just gotta love Excel!!

Overall I found the experience of uploading records very easy and I was very pleased that so many variations on a date were accepted. I really expected "Winter 2005" to be thrown back at me as I hadn't known which file to look in to see what would be accepted so thanks for the link for future reference.

Fiona

Fiona McCrory
CEDaR Website Officer
www.nmni.com/cedar

FionaCEDaR's Website