[
https://issues.apache.org/jira/browse/OAK-3645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15091768#comment-15091768
]
Tomek Rękawek edited comment on OAK-3645 at 1/11/16 1:16 PM:
-------------------------------------------------------------
I'll try to prepare expressions that returns current unix epoch for all
database engines. So far:
{noformat}
# db2
select cast (days(current_timestamp - current_timezone) - days('1970-01-01') as
integer) * 86400 + midnight_seconds(current_timestamp - current_timezone) from
sysibm.sysdummy1
(tested)
# postgres
select extract(epoch from now())::integer;
# mysql
select unix_timestamp();
# h2
## init
create alias if not exists unix_timestamp as $$ long unix_timestamp() { return
System.currentTimeMillis()/1000L; } $$;
## fetch
select unix_timestamp();
# derby (doesn't support timezones)
values {fn
timestampdiff(SQL_TSI_SECOND,timestamp('1970-1-1-00.00.00.000000'),
current_timestamp)};
# oracle
select (trunc(sys_extract_utc(systimestamp)) - to_date('01/01/1970',
'MM/DD/YYYY')) * 24 * 60 * 60 +
to_number(to_char(sys_extract_utc(systimestamp), 'SSSSS')) from dual;
# sql server
select datediff(second, dateadd(second, datediff(second, getutcdate(),
getdate()), '1970-01-01'), getdate());
{noformat}
was (Author: tomek.rekawek):
I'll try to prepare expressions that returns current unix epoch for all
database engines. So far:
{noformat}
db2
select cast (days(current_timestamp - current_timezone) - days('1970-01-01') as
integer) * 86400 + midnight_seconds(current_timestamp - current_timezone) from
sysibm.sysdummy1
postgres
select extract(epoch from now())::integer;
mysql
select unix_timestamp();
{noformat}
> RDBDocumentStore: server time detection for DB2 fails due to timezone/dst
> differences
> -------------------------------------------------------------------------------------
>
> Key: OAK-3645
> URL: https://issues.apache.org/jira/browse/OAK-3645
> Project: Jackrabbit Oak
> Issue Type: Technical task
> Components: rdbmk
> Affects Versions: 1.3.10, 1.2.8, 1.0.24
> Reporter: Julian Reschke
> Assignee: Tomek Rękawek
>
> We use {{CURRENT_TIMESTAMP(4)}} to ask the DB for it's system time.
> Apparently, at least with DB2, this might return a value that is off by a
> multiple of one hour (3600 * 1000ms) depending on whether the OAK instance
> and the DB run in different timezones.
> Known to work: both on the same machine.
> Known to fail: OAK in CET, DB2 in UTC, in which case we're getting a
> timestamp one hour in the past.
> At this time it's not clear whether the same problem occurs for other
> databases.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
