为什么 unix_timestamp 在 12 小时内错误地解析了这个? [英] Why is unix_timestamp parsing this incorrectly by 12 hours off?

问题描述

以下似乎不正确(spark.sql):

select unix_timestamp("2017-07-03T12:03:56", "yyyy-MM-dd'T'hh:mm:ss")
-- 1499040236

相比:

select unix_timestamp("2017-07-03T00:18:31", "yyyy-MM-dd'T'hh:mm:ss")
-- 1499041111

显然第一个在第二个之后.第二个似乎是正确的:

Clearly the first comes after the second. And the second appears to be correct:

# ** R Code **
# establish constants
one_day = 60 * 60 * 24
one_year = 365 * one_day
one_year_leap = 366 * one_day
one_quad = 3 * one_year + one_year_leap

# to 2014-01-01
11 * one_quad +
  # to 2017-01-01
  2 * one_year + one_year_leap + 
  # to 2017-07-01
  (31 + 28 + 31 + 30 + 31 + 30) * one_day + 
  # to 2017-07-03 00:18:31
  2 * one_day + 18 * 60 + 31
# [1] 1499041111

类似的计算表明第一个应该是 1499083436(由 as.integer(as.POSIXct('2017-07-03 12:03:56', tz = 'UTC')) in R)，并且 1499040236 应该对应于 2017-07-03 00:03:56.

A similar calculation shows the first should be 1499083436 (confirmed by as.integer(as.POSIXct('2017-07-03 12:03:56', tz = 'UTC')) in R), and that 1499040236 should correspond to 2017-07-03 00:03:56.

那么这里发生了什么?它当然看起来像一个错误.最后两次完整性检查 -- select unix_timestamp("2017-07-03T00:03:56", "yyyy-MM-dd'T'hh:mm:ss") 正确返回 1499040236;将中间的 T 替换为空格 对不正确的解析没有影响.

So what's happening here? It certainly looks like a bug. Two last sanity checks -- select unix_timestamp("2017-07-03T00:03:56", "yyyy-MM-dd'T'hh:mm:ss") correctly returns 1499040236; and replacing the T in the middle with a space has no effect on the incorrect parse.

由于它似乎在开发中已修复，我会注意到这是在 2.1.1 上.

Since it appears to be fixed in development, I'll note that this is on 2.1.1.

推荐答案

只是格式错误:

• 您的数据采用 0-23 小时格式(在 SimpleDateFormat 为 HH).
• 您使用对应于 1-24 小时格式的 hh 格式.

• Your data is in 0-23 hour format (denoted in SimpleDateFormat as HH).
• You use hh format which corresponds to 1-24 hour format.

事实上，在最新的 Spark 版本 (2.3.0 RC1) 中，它根本不会解析:

In fact, in the latest Spark version (2.3.0 RC1) it wouldn't parse at all:

spark.version

String = 2.3.0

spark.sql("""
  select unix_timestamp("2017-07-03T00:18:31", "yyyy-MM-dd'T'hh:mm:ss")""").show

+----------------------------------------------------------+
|unix_timestamp(2017-07-03T00:18:31, yyyy-MM-dd'T'hh:mm:ss)|
+----------------------------------------------------------+
|                                                      null|
+----------------------------------------------------------+

这篇关于为什么 unix_timestamp 在 12 小时内错误地解析了这个?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！