Is anyone importing dateearliestscalarvalue and datelatestscalarvalue in structured dates? Since these scalars are not calculated in imports (are they calculated at all yet? I'm still working in 2.0), I tried to load them myself. If I send 2011-01-01 in an import, it gets turned into 2010-12-31 16:00:00, and 2011-12-31 (for latest) is turned into 2011-12-30 16:00:00 in the postgres database. I don't think I want the dates to be changed like this, especially the year. I think I'd be happy with 00:00:00 for both times--unless I really want 23:59:59 for the end of the year time. I also don't know how to send a time. When I try to append a time onto the date (e.g., 00:00:00), the whole thing is ignored; if I try 00:00:00.00, I get a java error. What should I be doing here? Thanks, Susan

(The following is *entirely from memory*, rather than having been checked first-hand, so caveats are due): The timestamps stored in PostgreSQL are in 'timestamp without time zone' datatype columns. They're offset from UTC, in server local time, from what we've seen to date. Timestamps are converted back to UTC by the time they're emitted from the services, so ... * a date imported as '2011-01-01'; * becomes '2010-12-31 16:00:00' in the database when viewed via 'psql' (assuming an -800 offset from UTC, as in Pacific Standard Time); * and becomes 2011-01-01, at the first moment of the day (the stroke of midnight) in UTC, when emitted from the services, most likely as the timestamp '2011-01-01T00:00:00Z'. For the latest date, you might well want 23:59:59 on the last day of the year, to specify the last second of that year, and thus be inclusive of the entire year 2011. In that case, you might try importing the datelatestscalarvalue as '2011-12-31T23:59:59Z'; if that doesn't work, I'll be glad to explore other possibilities with you. On Sat, Mar 24, 2012 at 6:36 PM, Susan Stone <sstone@socrates.berkeley.edu> wrote: > Is anyone importing dateearliestscalarvalue and datelatestscalarvalue in > structured dates? > > Since these scalars are not calculated in imports (are they calculated at > all yet? I'm still working in 2.0), I tried to load them myself. If I send > 2011-01-01 in an import, it gets turned into 2010-12-31 16:00:00, and > 2011-12-31 (for latest) is turned into 2011-12-30 16:00:00 in the postgres > database. I don't think I want the dates to be changed like this, especially > the year. I think I'd be happy with 00:00:00 for both times--unless I really > want 23:59:59 for the end of the year time. > > I also don't know how to send a time. When I try to append a time onto the > date (e.g., 00:00:00), the whole thing is ignored; if I try 00:00:00.00, I > get a java error. > > What should I be doing here? > > Thanks, > Susan > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org

Thanks, Aron. I should have thought to try the T...Z business. Is there an example of a range search that compensates for the offset or gets it to be taken care of automatically? Susan > (The following is *entirely from memory*, rather than having been > checked first-hand, so caveats are due): > > The timestamps stored in PostgreSQL are in 'timestamp without time > zone' datatype columns. They're offset from UTC, in server local > time, from what we've seen to date. > > Timestamps are converted back to UTC by the time they're emitted from > the services, so ... > > * a date imported as '2011-01-01'; > * becomes '2010-12-31 16:00:00' in the database when viewed via 'psql' > (assuming an -800 offset from UTC, as in Pacific Standard Time); > * and becomes 2011-01-01, at the first moment of the day (the stroke > of midnight) in UTC, when emitted from the services, most likely as > the timestamp '2011-01-01T00:00:00Z'. > > For the latest date, you might well want 23:59:59 on the last day of > the year, to specify the last second of that year, and thus be > inclusive of the entire year 2011. In that case, you might try > importing the datelatestscalarvalue as '2011-12-31T23:59:59Z'; if that > doesn't work, I'll be glad to explore other possibilities with you. > > On Sat, Mar 24, 2012 at 6:36 PM, Susan Stone > <sstone@socrates.berkeley.edu> wrote: >> Is anyone importing dateearliestscalarvalue and datelatestscalarvalue in >> structured dates? >> >> Since these scalars are not calculated in imports (are they calculated >> at >> all yet? I'm still working in 2.0), I tried to load them myself. If I >> send >> 2011-01-01 in an import, it gets turned into 2010-12-31 16:00:00, and >> 2011-12-31 (for latest) is turned into 2011-12-30 16:00:00 in the >> postgres >> database. I don't think I want the dates to be changed like this, >> especially >> the year. I think I'd be happy with 00:00:00 for both times--unless I >> really >> want 23:59:59 for the end of the year time. >> >> I also don't know how to send a time. When I try to append a time onto >> the >> date (e.g., 00:00:00), the whole thing is ignored; if I try 00:00:00.00, >> I >> get a java error. >> >> What should I be doing here? >> >> Thanks, >> Susan >> >> _______________________________________________ >> Talk mailing list >> Talk@lists.collectionspace.org >> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >

I'm glad this came up. Has anyone confirmed we should be importing these scalar dates (for the structured date schema) in the 'timestamp without time zone' format e.g., '2011-12-31T23:59:59Z'? Has anyone confirmed that importing in that format then retains the correct dates in the system? Thanks, Chris On Mar 24, 2012, at 9:58 PM, sstone@socrates.berkeley.edu wrote: > Thanks, Aron. I should have thought to try the T...Z business. > > Is there an example of a range search that compensates for the offset or > gets it to be taken care of automatically? > > Susan > >> (The following is *entirely from memory*, rather than having been >> checked first-hand, so caveats are due): >> >> The timestamps stored in PostgreSQL are in 'timestamp without time >> zone' datatype columns. They're offset from UTC, in server local >> time, from what we've seen to date. >> >> Timestamps are converted back to UTC by the time they're emitted from >> the services, so ... >> >> * a date imported as '2011-01-01'; >> * becomes '2010-12-31 16:00:00' in the database when viewed via 'psql' >> (assuming an -800 offset from UTC, as in Pacific Standard Time); >> * and becomes 2011-01-01, at the first moment of the day (the stroke >> of midnight) in UTC, when emitted from the services, most likely as >> the timestamp '2011-01-01T00:00:00Z'. >> >> For the latest date, you might well want 23:59:59 on the last day of >> the year, to specify the last second of that year, and thus be >> inclusive of the entire year 2011. In that case, you might try >> importing the datelatestscalarvalue as '2011-12-31T23:59:59Z'; if that >> doesn't work, I'll be glad to explore other possibilities with you. >> >> On Sat, Mar 24, 2012 at 6:36 PM, Susan Stone >> <sstone@socrates.berkeley.edu> wrote: >>> Is anyone importing dateearliestscalarvalue and datelatestscalarvalue in >>> structured dates? >>> >>> Since these scalars are not calculated in imports (are they calculated >>> at >>> all yet? I'm still working in 2.0), I tried to load them myself. If I >>> send >>> 2011-01-01 in an import, it gets turned into 2010-12-31 16:00:00, and >>> 2011-12-31 (for latest) is turned into 2011-12-30 16:00:00 in the >>> postgres >>> database. I don't think I want the dates to be changed like this, >>> especially >>> the year. I think I'd be happy with 00:00:00 for both times--unless I >>> really >>> want 23:59:59 for the end of the year time. >>> >>> I also don't know how to send a time. When I try to append a time onto >>> the >>> date (e.g., 00:00:00), the whole thing is ignored; if I try 00:00:00.00, >>> I >>> get a java error. >>> >>> What should I be doing here? >>> >>> Thanks, >>> Susan >>> >>> _______________________________________________ >>> Talk mailing list >>> Talk@lists.collectionspace.org >>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >> > > > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org

Chris, I tested adding a record via rest with T...Z format with 00:00:00 for earliest and 23:59:59 for the latest. In the DB the times are offset by 8 hours; when the records are returned via REST, they show up as 00:00:00 and 23:59:59. But since I didn't import the scalars originally, I had to go into the database and insert (via sql update) the offset dates for all based on earliest year and latest year (we are only worried about years for import). They also come out as 00:00:00 and 23:59:59 etc. in REST get in almost all cases. Occasionally I get one that is one hour off and don't know why (so far I have noticed it for 1948). Leap year? Daylight savings? I am not worrying about this for now. Next migration they will go in with the rest of the import. I haven't looked at the advanced search to see if it is searching structured dates (correctly) using these fields. Susan > I'm glad this came up. Has anyone confirmed we should be importing these > scalar dates (for the structured date schema) in the 'timestamp without > time zone' format e.g., '2011-12-31T23:59:59Z'? Has anyone confirmed > that importing in that format then retains the correct dates in the > system? > Thanks, > Chris > > > On Mar 24, 2012, at 9:58 PM, sstone@socrates.berkeley.edu wrote: > >> Thanks, Aron. I should have thought to try the T...Z business. >> >> Is there an example of a range search that compensates for the offset or >> gets it to be taken care of automatically? >> >> Susan >> >>> (The following is *entirely from memory*, rather than having been >>> checked first-hand, so caveats are due): >>> >>> The timestamps stored in PostgreSQL are in 'timestamp without time >>> zone' datatype columns. They're offset from UTC, in server local >>> time, from what we've seen to date. >>> >>> Timestamps are converted back to UTC by the time they're emitted from >>> the services, so ... >>> >>> * a date imported as '2011-01-01'; >>> * becomes '2010-12-31 16:00:00' in the database when viewed via 'psql' >>> (assuming an -800 offset from UTC, as in Pacific Standard Time); >>> * and becomes 2011-01-01, at the first moment of the day (the stroke >>> of midnight) in UTC, when emitted from the services, most likely as >>> the timestamp '2011-01-01T00:00:00Z'. >>> >>> For the latest date, you might well want 23:59:59 on the last day of >>> the year, to specify the last second of that year, and thus be >>> inclusive of the entire year 2011. In that case, you might try >>> importing the datelatestscalarvalue as '2011-12-31T23:59:59Z'; if that >>> doesn't work, I'll be glad to explore other possibilities with you. >>> >>> On Sat, Mar 24, 2012 at 6:36 PM, Susan Stone >>> <sstone@socrates.berkeley.edu> wrote: >>>> Is anyone importing dateearliestscalarvalue and datelatestscalarvalue >>>> in >>>> structured dates? >>>> >>>> Since these scalars are not calculated in imports (are they calculated >>>> at >>>> all yet? I'm still working in 2.0), I tried to load them myself. If I >>>> send >>>> 2011-01-01 in an import, it gets turned into 2010-12-31 16:00:00, and >>>> 2011-12-31 (for latest) is turned into 2011-12-30 16:00:00 in the >>>> postgres >>>> database. I don't think I want the dates to be changed like this, >>>> especially >>>> the year. I think I'd be happy with 00:00:00 for both times--unless I >>>> really >>>> want 23:59:59 for the end of the year time. >>>> >>>> I also don't know how to send a time. When I try to append a time onto >>>> the >>>> date (e.g., 00:00:00), the whole thing is ignored; if I try >>>> 00:00:00.00, >>>> I >>>> get a java error. >>>> >>>> What should I be doing here? >>>> >>>> Thanks, >>>> Susan >>>> >>>> _______________________________________________ >>>> Talk mailing list >>>> Talk@lists.collectionspace.org >>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>> >> >> >> >> _______________________________________________ >> Talk mailing list >> Talk@lists.collectionspace.org >> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org > >