dbGetQuery返回相差一个小时的日期 [英] dbGetQuery returns date that differs by an hour

问题描述

在R中，我正在运行以下查询以检索数据:

In R I am running the following query to retrieve data:

test <- dbGetQuery(conn = GetConnection("default"), statement = "SELECT  PK_FK_RW_BOND_HOLDING_VAL_BOND, PK_CASHFLOW_DAT FROM RW_CASH_FLOWS_ON_BONDS WHERE PK_FK_RW_BOND_HOLDING_VAL_BOND = 'AT0000385745'
                   OR PK_FK_RW_BOND_HOLDING_VAL_BOND = 'RU000A0JV7J9'")

这将返回

PK_FK_RW_BOND_HOLDING_VAL_BOND  PK_CASHFLOW_DAT
RU000A0JV7J9                    2018-01-14 23:00:00
RU000A0JV7J9                    2017-01-14 23:00:00
RU000A0JV7J9                    2019-08-01 00:00:00
RU000A0JV7J9                    2019-01-31 23:00:00
RU000A0JV7J9                    2018-08-01 00:00:00
RU000A0JV7J9                    2018-01-31 23:00:00
AT0000385745                    2017-08-01 00:00:00
AT0000385745                    2017-01-31 23:00:00

，其中PK_CASHFLOW_DAT是("POSIXct" "POSIXt")类.另一方面，如果我在SQL(Toad for Oracle)中运行完全相同的查询，则会返回

where the PK_CASHFLOW_DAT is of the class ("POSIXct" "POSIXt"). On the other hand, if I run the exact same query in SQL (Toad for Oracle) it returns

PK_FK_RW_BOND_HOLDING_VAL_BOND  PK_CASHFLOW_DAT
RU000A0JV7J9                    1-8-2019
RU000A0JV7J9                    1-2-2019
RU000A0JV7J9                    1-8-2018
RU000A0JV7J9                    1-2-2018
RU000A0JV7J9                    1-8-2017
RU000A0JV7J9                    1-2-2017
AT0000385745                    15-1-2018
AT0000385745                    15-1-2017

现在，PK_CASHFLOW_DAT的DateType是DATE.我的计算机和服务器的时区均为英国.

Now, the DateType of PK_CASHFLOW_DAT is DATE. The timezone of my computer as well as that of the server are UK.

我的问题是双重的:
1.为什么首先发生这种情况.
2.实际上，我需要将日期设置为字符格式.有什么简单的方法可以让我们要求DateType Date在R中以character的形式接收，而不是("POSIXct" "POSIXt")?

My question is twofold:
1. Why is this happening in the first place.
2. I actually need the date to be in a character format. Is there some easy way in which we request the DateType Date to be received as character in R, instead of ("POSIXct" "POSIXt")?

谢谢！

推荐答案

此问题是由于操作系统和oracle会话的默认时区设置引起的. 您可以覆盖这些设置.就我而言，以下规范可以解决问题.

This problem is due to the default time zone settings of your operating system and your oracle session. You can overwrite these settings. In my case the following specification does the trick.

Sys.setenv(TZ='CET')        # Operating system
Sys.setenv(ORA_SDTZ='CET')  # Oracle Session

我在所有R脚本的开头都包含了这些设置，但是也许还有一种方法可以使这些设置成为新的默认设置.

I include these settings in the beginning of all my R scripts, but possibly there is also a way to make these settings your new default.

以下是有关Oracle时区参数的更多信息:

Here is more information on the Oracle time zone parameter: Setting the Session Time Zone

这篇关于dbGetQuery返回相差一个小时的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！