什么是适当的日期格式，以便字典顺序与时间顺序相同? [英] What is the appropriate date formatting so the lexicographic ordering is the same as time ordering?

问题描述

在我的 SQLite 数据库中，没有 Date 数据类型，所以我必须以文本格式存储时间戳.

In my SQLite database, there is no Date datatype, so I have to store timestamps in text format.

格式 yyyy-MM-dd HH:mm 是否会产生正确的排序，这样当您按字典顺序对其进行排序(通过执行正常排序 ASC 或 DESC)时，它还按时间值排序天生的?

Does the format yyyy-MM-dd HH:mm result in the correct ordering such that when you sort it lexicographically (by doing a normal sort ASC or DESC), it also orders by time value inherently?

推荐答案

你的格式是正确的想法；按字母顺序排序时，它也是按时间顺序排列的.

Your format is the right idea; when sorted alphabetically it is also chronological.

您可以更进一步，获得该格式的更好版本，即标准格式，使您的工作更简单、更轻松.

You can take a step further, for a better version of that format, a standard format, to make your work simpler and easier.

ISO 8601 标准为表示日期的文本定义了多种实用的合理格式——时间相关的值.

The ISO 8601 standard defines a variety of practical sensible formats for text representing date-time related values.

对于组合的日期和时间，格式为:

For a date and time combined the format is:

YYYY-MM-DDTHH:MM:SS.SZ

YYYY-MM-DDTHH:MM:SS.SZ

例如:

2016-04-28T18:22:20.123Z

2016-04-28T18:22:20.123Z

这种作为字符串的格式会根据您的需要按时间顺序排序.

This format as a string sorts chronologically as you need.

中间的 T 将日期部分与时间部分分开.末尾的 Z 是 Zulu 的缩写，意思是 UTC.

The T in the middle separates the Date portion from the Time portion. The Z on the end is short for Zulu which means UTC.

通常，最佳做法是将日期时间值转换为 UTC 以用于存储和数据库.您的 JDBC 驱动程序可能会为您执行此操作，但我不了解 SQLite.

Generally, best practice is to convert your date-time values to UTC for storage and database. Your JDBC driver likely does that for you but I don't know about SQLite.

java.time框架内置于 Java 8 及更高版本中.大部分功能都向后移植到 Java 6 &7 在 ThreeTen-Backport 项目中，并在 ThreeTenABP 项目.

The java.time framework is built into Java 8 and later. Much of that functionality is back-ported to Java 6 & 7 in the ThreeTen-Backport project, and further adapted to Android in the ThreeTenABP project.

这些新类取代了旧的 java.util.Date/.Calendar 和相关类，这些类已被证明设计不佳且麻烦.

These new classes supplant the old java.util.Date/.Calendar and related classes that have proven to be poorly designed and troublesome.

这些类在解析/生成日期时间值的文本表示时默认使用 ISO 8601 格式.在 Stack Overflow 上搜索许多示例.

These classes use ISO 8601 formats by default when parsing/generating textual representations of date-time values. Search Stack Overflow for many examples.

即时 是 UTC 时间线上的一个时刻，分辨率为 纳秒.

An Instant is a moment on the timeline in UTC with a resolution of nanoseconds.

Instant instant = Instant.now();

只需调用 toString 生成该值的字符串表示形式.

Simply call toString to generate a String representation of that value.

String stringForDatabase = instant.toString();

在 Java 8 中，由于 Clock 界面，为一秒的分数保留 3 个小数位.例如，2016-04-29T00:12:57.123Z.在 Java 9 及更高版本中有 Clock 的现代实现，能够在计算机硬件时钟支持的范围内捕获最多 9 个小数位(纳秒)的当前时刻.

In Java 8 the current moment is captured to only milliseconds resolution due to legacy implementation of the Clock interface, for 3 decimal places for the fraction of a second. For example, 2016-04-29T00:12:57.123Z. In Java 9 and later has a modern implementation of Clock, able to capture the current moment in up to 9 decimal places (nanoseconds) as far as is supported by your computer’s hardware clock.

Instant:toString 使用的默认格式化程序会用 0、3、6 或 9 位数字打印秒的小数部分，根据需要来表示小数部分的非零部分一秒钟.所有这些按字母顺序排序 &按照要求按时间顺序排列，因此您可以将其中任何一个存储在您的数据库中.

The default formatter used by Instant:toString prints the fraction of a second with 0, 3, 6, or 9 digits, as many as needed to represent the non-zero portion of the fraction of a second. All of these sort alphabetically & chronologically as requested, so you could store any of these in your database.

• 2016-04-29T00:12:57Z
• 2016-04-29T00:12:57.123Z
• 2016-04-29T00:12:57.123456Z
• 2016-04-29T00:12:57.123456789Z

这些都直接解析回一个 Instant 实例.因此，无需像问题中那样定义自己的格式模式.

These all parse directly back into an Instant instance. So no need to bother with defining your own formatting pattern as in the Question.

Instant instant = Instant.parse( "2016-04-29T00:12:57.123456789Z" );

要查看特定地点的挂钟时间，请应用时区 (ZoneId) 以获取 ZonedDateTime.

To see the wall-clock time for a particular place, apply a time zone (ZoneId) to get a ZonedDateTime.

ZoneId zoneId = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );

提取一个 Instant 以存储回数据库.

Extract an Instant for storage back into the database.

Instant instant = zdt.toInstant();
String forDatabase = instant.toString();

这篇关于什么是适当的日期格式，以便字典顺序与时间顺序相同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！