Date

Solr 2 oracle date indexing timezone handling and probable issues

Apache Solr provides a field definition type for datetime values called solr.TrieDateField (TrieDateField) that is based on an efficient compare-/sort-representation. Being an extension-/derived-class to the well known solr.DateField (DateField) up to Solr 4.x, solr.TrieDateField does replace solr.DateField for Solr releases > 5.0 . Using one or the other date field preassumes one important convention: to handle any value passed around or processed within Solr as UTC (Coordinated Universal Time) or zulu time (Z appended) such that all that timezone-detection- and timezone-math-hassle can be avoided. Solr thus exclusively allows values given to the DataImportHandler as defined in ISO 8601, “1995-12-31T23:59:59Z” as an example. However, iff you do not pass the value as a string but as a database date or timestamp w/o tz datatype within an sql select statement to DataImportHandler, secondary Solr-side processing may have to be taken into account.
(more…)

Getting the utc and unix time in milliseconds from oracle

It is quite necessary these days, especially in the globalization/24*7-worlds to pass around (unix-)timestamps in a form being defined as:

The difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.

Indeed there is a dedicated method for this in the Java editions since long being declared as public static long currentTimeMillis() of class System. Also, Web-environments based on Javascript and the like offer native logic to transform a given UTC-timestamp to the some clients timezone and timestamp, respectively.

Delivering that msecs-utc-value from Oracle may seem straightforward at first, but may become more and more a jungle of options and proposed possibilities of conversion, extraction and so on. It is especially puzzling iff one actually has no timestamp with time zone instance at hand and needs to start off from existent date or timestamp w/o time zone column data. That is, data having had no time zone stored upon inserting or updating, such that calculating back to UTC is impossible without an edicated guess.

Hence, the only option for original date or timestamp w/o time zone data is using the Oracle from_tz function (save yourself from checking out new_time, it supports a very limited number of time zones only), passing in a timestamp w/o time zone and a time zone region known to Oracle. Do not just pass in a derived time zone value, e.g. sessiontimestamp, because Oracle takes this for granted without actually looking back in time, regarding daylight saving and things. For example, in 1978 daylight saving has not yet been introduced in Germany but in 1988 it was in use:

-- what's in sessiontimezone currently ?
select sessiontimezone from dual;
+02:00

-- that's ok, an educated guess on the original time zone
-- actually the only option you have
select from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin') as xyz1,
  from_tz(cast(to_date('01.08.1988 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin') as xyz1
from dual;
01/08/1978 12:00:00,000000 +01:00	01/08/1988 12:00:00,000000 +02:00

-- possible and likely given in the oracle docs but wrong, oracle just follows
--   your specification without actually looking back in time
-- iff you do this in winter, the result may become wrong again in another
--   way because sessiontimezone will now read +01:00
select from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    sessiontimezone) as xyz1,
  from_tz(cast(to_date('01.08.1988 12', 'dd.mm.yyyy hh24') as timestamp),
    sessiontimezone) as xyz1
from dual;
01/08/1978 12:00:00,000000 +02:00	01/08/1988 12:00:00,000000 +02:00

Once you have derived this time zone aware value, retieving the UTC time is just employing a supplied function and a cast like this:

select cast(sys_extract_utc(
  from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin')) as date) as xyz1
from dual;

Finally, substract any time being passed before january 1st 1970 and calculate the msecs-value, preassuming that one Oracle day is, in numbers, exactly = 1.

select to_number(cast(sys_extract_utc(
  from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin')) as date) - to_date('01.01.1970','dd.mm.yyyy'))
    * (24 * 60 * 60 * 1000) as xyz1
from dual;

have fun.