在sqldf中将整数值转换为datetime [英] Convert an integer value to datetime in sqldf

问题描述

我正在使用sqldf库返回一个数据帧，该数据帧具有不同的值，并且仅返回date列的最大值。数据框如下所示

I am using sqldf library to return a data frame with distinct values and also only the max of the date column. The data frame looks like this

+------+----------+--------+-----------------+
| NAME |   val1   |  val2  |      DATE       |
+------+----------+--------+-----------------+
| A    |  23.7228 | 0.5829 | 11/19/2014 8:17 |
| A    |  23.7228 | 0.5829 | 11/12/2014 8:16 |
+------+----------+--------+-----------------+

当我尝试运行以下代码以获取具有最大日期的不同值时

When I try to run the below code to get the distinct values with max date

df <-  sqldf("SELECT DISTINCT NAME, val1, val2, MAX(DATE) FROM Table")

我将其作为输出。

+------+----------+--------+-----------------+
| NAME |   val1   |  val2  | MAX(DATE)       |
+------+----------+--------+-----------------+
| A    |  23.7228 | 0.5829 | 1416406625      |
+------+----------+--------+-----------------+

请让我知道如何转换最后一列，它是一个整数以取回我的日期时间格式。

Please let me know how do I convert the last column, which is an integer to get back my datetime format.

推荐答案

下次，请以可重复的形式提供您的输入。我这次为您完成了此操作。问题中的SQL代码也有一个SQLite语法错误，我已经在下面修复。

Next time please provide your input in reproducible form. I have done it this time for you below. Also the SQL code in the question has an SQLite syntax error which I have fixed below.

最简单的方法是使用名称 DATE 作为输出列，在这种情况下，sqldf会得出其与 DATE 输入列相同的类型。 SQLite没有日期和时间类型，因此当sqldf与SQLite一起使用时，sqldf无法知道返回的是日期时间。 sqldf使用一些启发式方法进行猜测，例如刚刚讨论的一种。

The easiest way to get this right is to use the name DATE for the output column in which case sqldf will figure that its of the same type as the DATE input column. SQLite has no date and time types so there is no way for sqldf to know that what is being returned is a datetime when using sqldf with SQLite. sqldf uses some heuristics to guess such as the one just discussed.

library(sqldf)

Lines <- "NAME,val1,val2,DATE     
A,23.7228,0.5829,11/19/2014 8:17 
A,23.7228,0.5829,11/12/2014 8:16"

Table <- read.csv(text = Lines, as.is = TRUE)
Table$DATE <- as.POSIXct(Table$DATE, format = "%m/%d/%Y %H:%M")

sqldf("SELECT DISTINCT NAME, val1, val2, MAX(DATE) DATE FROM 'Table'")

给予：

   NAME    val1   val2                DATE
1     A 23.7228 0.5829 2014-11-19 08:17:00

如果我们使用H2使用sqldf时，由于H2确实支持日期和时间类型，因此我们不会遇到这些问题，因此sqldf不必猜测。 SQL查询的语法也与H2中的语法相同。使用上面显示的表 data.frame：

If we used H2 with sqldf then we would not have these problems since H2 does support date and time types so sqldf does not have to guess. Also the syntax of your SQL query works as is in H2. Using the Table data.frame shown above:

library(RH2)
library(sqldf)

sqldf("SELECT DISTINCT NAME, val1, val2, MAX(DATE) DATE FROM Table")

给出：

   NAME    val1   val2           MAX(DATE)
1     A 23.7228 0.5829 2014-11-19 08:17:00

这篇关于在sqldf中将整数值转换为datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！