0

I am using the ROracle package to pull data for a project, and before doing the pull the code syncs the R session and Oracle timezones to UTC:

Sys.setenv(TZ = "00:00")
Sys.setenv(ORA_SDTZ = "00:00")

However, based on what I've seen, our Oracle database appears to be set to Pacific time (our local timezone). So based on this I've tried changing the above code to instead sync to Pacific time.

Sys.setenv(TZ = "America/Los_Angeles")
Sys.setenv(ORA_SDTZ = "America/Los_Angeles")

I tried my original UTC pull using dates of 2018-01-01 to 2020-12-31 and my Pacific pull for that same date range and they both seem to work fine. However when I try changing the dates to 1999-01-01 to 2001-12-31 it works fine if the timezone is set to UTC but once I change it to Pacific timezone I get this error:

Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch,  : 
  Error in try({ : 
  ORA-01878: specified field not found in datetime or interval

I tried testing out my query and for some peculiar reason removing a particular variable called ATIME from the SELECT statement allows the code to work even when the timezone is set to Pacific. Does anyone have any insights into this? Could it be due to the datatype of this ATIME variable? My SQL developer program seems to indicate that the variable has a datatype of DATE. Could it perhaps be due to daylights saving time being a thing with the Pacific timezone but not with the UTC timezone? Surely if daylight saving was the issue the Pacific time code would not work with the 2018-2020 pull, but it does appear to work?

M. Y.
  • 35
  • 4
  • 1
    This may be due to DST depending on calculation you use in your script. Please, check this question: [Oracle date compare broken because of DST](https://stackoverflow.com/questions/22305466/oracle-date-compare-broken-because-of-dst) – astentx Jul 14 '21 at 22:40
  • If `atime` is a date, your query or how you 'pull' it is converting it to a timestamp with time zone data type - possibly implicitly. Do you have any information about how that column is populated, which might tell you what time zone those date values are intended to be (nominally) in? They might have been normalised to UTC for instance, or have been set using sysdate (so could be assumed to be in DB time zone), or by current_date which could be any time zone. They aren't all valid for LA, anyway, it seems. – Alex Poole Jul 14 '21 at 22:56
  • Hi, not sure, I've run "select to_char(sysdate,'HH24:MI' ) from dual;" and that indicates the oracle DB is in pacific time, but I'm not so sure how to check that particular variable. I would likely need to check with the group that manages the database, could it potentially be due to a change in how the column was being populated at some point in time, and that would perhaps explain why the code works when set to Pacific for 2018-2020 but not 1999-2001? I guess I would wonder how it's deciding to convert it to a timestamp w/ timezone datatype? The query simply uses * to select all columns. – M. Y. Jul 14 '21 at 23:20
  • [R seems to be converting it](https://oralytics.com/2019/12/20/r-roracle-and-oracle-date-formats/). Which I guess you know about from setting the variables already. If those specific dates can/should be treated as a specific time zone you could explicitly convert them as part of the query - e.g. `from_tz(cast(atime as timestamp), 'UTC')`. But you'd need to know which time zone to use. – Alex Poole Jul 15 '21 at 07:15
  • As to why it seems to work with more cent dates... if the dates are supposed to nominally be PST/PDT, maybe the [DST rule change in 2007](https://en.wikipedia.org/wiki/Pacific_Time_Zone#Daylight_time) is the issue? Oracle should handle that though, if it's OK with recent dates; and seems unlike R or your OS wouldn't. So it's back to how the column was populated really; and that may mean there are still discrepancies you can't fix with a single zone. – Alex Poole Jul 15 '21 at 07:22
  • Hi, thats actually something I'm wondering, I ran my programs both set to UTC and Pacific and yet they both appeared to be identical when using all.equal... I am wondering if setting the timezone to Pacific is even really necessary now... which would let me avoid this issue with this particular column causing crashes. Surely if the database is in Pacific time and I run it with the timezones set to UTC I should be finding differences vs the Pacific time runs? – M. Y. Jul 15 '21 at 14:17
  • If you've said that either (a) both TZ and ORA_SDTZ are UTC, or (b) both TZ and ORA_SDTZ are America/Pacific, then the time-zone aware timestamps you end up with will represent the same point in local time - I think; it's confusing *8-) Or at least, those that are valid times in both UTC and Pacific. It doesn't matter what the DBTIMEZONE is, just SESSIONTIMEZONE - which you are specifying via ORA_SDTZ. – Alex Poole Jul 15 '21 at 15:17

0 Answers0