H2 数据库排序字符串时间戳，以 yyyy-MM-dd'T'hh：mm：ss.SSSSSSSSS'Z'

Question

我需要使用java 使用h2查询对yyyy-MM-dd'Th:MM:ss.SSSSSSS'Z'格式的字符串日期时间进行排序

TIME_ORDER
2021-03-08T01:00:01.000000800Z
2021-03-08T01:02:48.000000735Z
2021-03-08T12:58:02.000000016Z
2021-03-08T01:03:48.000000735Z
2021-03-08T01:04:48.000000735Z

如何在H2中对这种格式进行排序，我尝试了

SELECT TIME_ORDER FROM TABLE1 order by PARSEDATETIME(TIME_ORDER,'yyyy-MM-dd'T'HH:mm:ss.SSSZ')

我在中得到以下错误

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement PARSEDATETIME(TIME_ORDER,'yyyy-MM-dd'T[*]'HH:mm:ss.SSSZ'))"; expected "[, ::, AT, FORMAT, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:

尝试多次组合毫无线索，任何建议或解决方案都将不胜感激。提前感谢

Answer 1

在H2中，您可以将带有ISO时间戳的字符串直接转换为日期时间数据类型，无需干扰任何函数：

SELECT TIME_ORDER FROM TABLE1
ORDER BY CAST(TIME_ORDER AS TIMESTAMP(9) WITH TIME ZONE);

注意：您需要指定精确的小数秒精度9，因为默认值为6，这对于您的样本数据来说是不够的，而且使用TIMESTAMP WITH TIME ZONE更安全，因为普通TIMESTAMP无法区分从夏令时到正常时间的转换和其他向后转换期间的时间戳(如果您的本地时区有时间戳的话(。

PARSEDATETIME仅支持3个小数位数，并且存在其他问题。在您的情况下，编译错误是由'的错误使用引起的，这些字符需要在SQL字符串文本中指定两次，而且您的模式末尾有不正确的原始Z，您可以解决这些问题，但无论如何都不要使用它，由于此函数的限制，您很容易使用它获得不正确的排序。

您还应该考虑对TIME_ORDER列使用TIMESTAMP(9) WITH TIME ZONE数据类型，而不是字符串数据类型。

Answer 2

字母顺序为时间顺序

如果您的数据保证始终：

• 根据需要在小数秒内使用填充空格，以获得小数分隔符后的九位数字，和
• 完全使用该格式(十进制分隔符始终使用FULL STOP而不是COMMA，这与ISO 8601标准中建议的逗号偏好相反(

…然后您可以简单地将文本值排序为文本。不需要做任何额外的事情。在这种情况下，字母顺序也是时间顺序。

排序

如果这两个项目符号规则并不总是这样，那么您必须使用其中一个：

• 叶夫根尼·梁扎诺夫的回答中显示的内部(H2(方法，或
• Arvind Kumar Avinash的回答中所示的外部(Java(方法

使用类型Luke

最稳健的解决方案是使用日期-时间类型存储日期-时间值。将日期时间值存储为文本通常是一个糟糕的表格设计选择。

SQL标准定义了一个日期-时间类型数组。H2实现了这些类型。所以使用这些类型。

您的价值观以Z结尾。这意味着日期-时间是指通过距离UTC 0小时-分-秒的偏移量来查看的。通常我们简单地称之为"；UTC时间"；。Z发音为"祖鲁"，如"祖鲁时间"，意思是"UTC时间"。因此，H2中适合您输入的类型是TIMESTAMP WITH TIME ZONE

Answer 3

您可以使用JDBC检索它们，然后使用Java代码进行排序，而不是使用SQL对它们进行排序

演示：

import java.time.OffsetDateTime;
import java.util.stream.Stream;
public class Main {
public static void main(String[] args) {
Stream.of(
"2021-03-08T01:00:01.000000800Z",
"2021-03-08T01:02:48.000000735Z",
"2021-03-08T12:58:02.000000016Z",
"2021-03-08T01:03:48.000000735Z",
"2021-03-08T01:04:48.000000735Z"
)
.map(s -> OffsetDateTime.parse(s))
.sorted()
.forEach(System.out::println);      
}
}

输出：

2021-03-08T01:00:01.000000800Z
2021-03-08T01:02:48.000000735Z
2021-03-08T01:03:48.000000735Z
2021-03-08T01:04:48.000000735Z
2021-03-08T12:58:02.000000016Z

在线演示