Part Attributes Date - allow month only and year only dates

[Nicole-Ridgwell-NMMNHS]

The part attributes date field will except only dates that have year month and day. Is there a reason for restricting this field? I'm trying to upload a bunch of part and part attribute data right now and a lot of those dates I only know to year. I'd rather indicate 2019 unambiguously rather than enter something ambiguous like 2019-01-01 or use a roundabout method like leaving date blank and putting 2019 in remarks.

[dustymc]

I don't think it's a restriction, just a (probably legacy in this case) datatype choice. We have occasionally chosen to retain DATE as a restriction - eg "loan due sometime next year" isn't a welcome possibility - but I don't remember that here.

I don't think I have much of an opinion regarding which is "correct" for this.

It would be a fair bit of code to chase down, but I don't foresee any technical problems in this direction. (Going back to DATE is not possible though; we should be sure before proceeding.)

[campmlc]

I support this.

On Mon, Mar 8, 2021, 3:50 PM dustymc notifications@github.com wrote:

• [EXTERNAL]*

I don't think it's a restriction, just a (probably legacy in this case) datatype choice. We have occasionally chosen to retain DATE as a restriction - eg "loan due sometime next year" isn't a welcome possibility

• but I don't remember that here.

I don't think I have much of an opinion regarding which is "correct" for this.

It would be a fair bit of code to chase down, but I don't foresee any technical problems in this direction. (Going back to DATE is not possible though; we should be sure before proceeding.)

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/3505#issuecomment-793143512, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADQ7JBC5R55JIUDT74JFXGLTCVICPANCNFSM4Y2IFHWQ .

[Jegelewicz]

I support it as well, and think it should also apply to catalog item attributes and locality attributes. Anywhere else?

[dustymc]

Anywhere else?

None of those....

Here's a partial list.

arctosprod@arctos>> select table_name,column_name from information_schema.columns where data_type='timestamp without time zone' order by table_name,column_name;
           table_name           |       column_name        
--------------------------------+--------------------------
 address                        | s$lastdate
 agent                          | created_date
 agent_rank                     | rank_date
 agent_relations                | created_on_date
 agent_status                   | status_reported_date
 annotations                    | annotate_date
 api_log                        | request_time
 archive_name                   | create_date
 blacklist                      | lastdate
 blacklist                      | listdate
 blacklist_subnet               | insert_date
 blacklist_subnet               | lastdate
 blacklisted_entry_attempt      | timestamp
 browse                         | insdate
 bulk_pre_loc_attrs             | enteredtobulkdate
 bulkloader                     | enteredtobulkdate
 bulkloader_attempts            | tstamp
 bulkloader_clone               | enteredtobulkdate
 bulkloader_deletes             | enteredtobulkdate
 bulkloader_stage               | enteredtobulkdate
 cache_anygeog                  | lastdate
 cache_publication_sdata        | last_date
 cache_sysstats_coln            | lastdate
 cache_sysstats_global          | lastdate
 coll_evt_attr_archive          | changedate
 coll_obj_cont_hist             | installed_date
 coll_object                    | coll_object_entered_date
 coll_object                    | last_edit_date
 collecting_event               | last_dup_check_date
 collecting_event_archive       | changedate
 container                      | parent_install_date
 container_check                | check_date
 container_environment          | check_date
 container_history              | install_date
 email_log                      | request_time
 encumbrance                    | expiration_date
 encumbrance                    | made_date
 error_log                      | request_time
 exit_link                      | when_date
 loan                           | closed_date
 loan                           | return_due_date
 loan_item                      | reconciled_date
 locality                       | last_dup_check_date
 locality                       | s$lastdate
 locality_archive               | changedate
 locality_attribute_archive     | changedate
 media_flat                     | lastdate
 media_keywords                 | lastdate
 media_labels                   | assigned_on_date
 media_relations                | created_on_date
 object_condition               | determined_date
 ocr_text                       | ocr_date
 ocr_text                       | try_date
 permit                         | exp_date
 permit                         | issued_date
 query_log                      | request_time
 query_stats                    | create_date
 request_log                    | request_time
 scheduler_log                  | request_time
 shipment                       | shipped_date
 specimen_event                 | assigned_date
 specimen_part_attribute        | determined_date
 taxon_name                     | created_date
 taxon_refresh_log              | lastfetch
 taxon_relations                | last_refresh_date
 taxon_term                     | lastdate

[Jegelewicz]

None of those

? They all have determination dates just like part attributes. Why don't they show up in the list?

[Jegelewicz]

It seems like the list includes mostly system-generated dates, which should be more than just a year. It is the determination dates for attributes, for identifications, for specimens events for which we may only have a year or month+year and I think we would benefit from recording what we know instead of making up something (adding a month and day) in order to make the data fit.

[dustymc]

Why don't they show up in the list?

They're not DATE datatype. https://handbook.arctosdb.org/documentation/dates.html

specimens events

If you mean assigned date, there's

1. Little value in legacy data - not much could have happened on 1960-12-31 that wasn't possible on 1960-01-01
2. Much value in recent data. 2000-05-01 is the elephant in the room, but also things like version dates of GIS programs and satellite cartography can be critical to understanding data.

So my vote remains for DATE there, even if it means we have to lie about what I consider low-quality data. Absolutely not the only viewpoint, but the implications of losing precision are worth careful consideration.

[Jegelewicz]

OK then, I think this needs the flexibility of year or month+year

table_name	column_name
object_condition	determined_date

BUT - why doesn't part attributes determined date show up in the list? If it is not DATE, then why does it insist on YYYY-MM-DD which is what started this issue in the first place? What datatype is that field and what options are available to us?

[dustymc]

object_condition

No serious objections from me, but someone might like to correlate it with loan dates (which are - correctly I believe - DATE) - @AJLinn ??

part attributes determined date

What datatype is that field

DATE (ish....). This is a request to change it to varchar controlled by https://github.com/ArctosDB/PG_DDL/blob/master/function/is_iso8601.sql

what options are available to us

Currently, DATE (which we round to day-precision), https://handbook.arctosdb.org/documentation/dates.html if we change it.

[AJLinn]

No serious objections from me, but someone might like to correlate it with loan dates (which are - correctly I believe - DATE) - @AJLinn ??

Yes, definitely the condition fields have specific yyyy-mm-dd because of exactly what you say, correlating to an exhibition period, a loan period, etc. We will sometimes use those data to tabulate hours of light exposure for light sensitive pieces so knowing the exact date a condition report was done is important (for indicating when that exposure starts/stops).

That said, I've yet to start using the part attribute "condition report" because all my existing reports are embedded in the part-condition field, which just has long flowing segments of text separated with dates. Now that I know it's there (sorry I've been less that attentive since fall) I'll start using it for better precision, most importantly with the specific date potential.

[Nicole-Ridgwell-NMMNHS]

@AJLinn is that a request to keep YYYY-MM-DD date format for part attributes?

[Jegelewicz]

I think we should be clear that allowing YYYY or YYYY-MM dates doesn't preclude using YYYY-MM-DD.

[dustymc]

allowing YYYY or YYYY-MM dates doesn't preclude using YYYY-MM-DD

Correct, but it does require good procedures if you want to easily compare those to DATE objects. If you always use the date-picker (defaults to YYYY-MM-DD) then you can expect it to just work. If you have some year-precision and some second-with-timezone and some second-without-timezone and ..... then you should also be somehow prepared to deal with that diversity.

Seems pretty low risk/high reward once I see it spelled out....

[Nicole-Ridgwell-NMMNHS]

Can we move forward with this now?

[Nicole-Ridgwell-NMMNHS]

I really need to get my data uploaded, so if we're going to fix the date format can that happen soon?

[Jegelewicz]

@Nicole-Ridgwell-NMMNHS I have a feeling that @dustymc might be on spring break? But if he isn't, he will see this....

[Jegelewicz]

Added to issues meeting agenda.

[dustymc]

spring break

Yea but I'm not very good at it...

I probably won't make the Issues meeting. I think all my concerns are above - quick discussion can't hurt, then just needs prioritized if nobody sees a reason not to.

[Jegelewicz]

Stop checking in - just tell us that you'll be gone for a week so we don't pester you!

[Jegelewicz]

AWG says go ahead.

[Nicole-Ridgwell-NMMNHS]

Thank you!

[Nicole-Ridgwell-NMMNHS]

I'm still getting an error on the date format in the parts bulkloader:

[dustymc]

try now

[Nicole-Ridgwell-NMMNHS]

Thanks, that worked! I noticed that the definitions and documentation in the bulk load tool will need to be updated:

[dustymc]

thx, will be in next release