在SQLite数据库（Delphi）中存储datetime值的最佳方式 [英] Optimal way to store datetime values in SQLite database (Delphi)

问题描述

我将在SQLite数据库（使用Delphi和DISqlite库）中存储datetime值。数据库的性质使得它永远不需要在计算机或系统之间传输，因此互操作性不是约束。我的焦点是阅读速度。 datetime字段将被索引，我将在其上进行很多的搜索，以及按顺序读取数千个datetime值。

由于SQLite没有明确的datetime值的数据类型有几个选项：

• 使用REAL数据类型并直接存储Delphi的TDateTime值：最快，不从加载字符串转换;不可能使用数据库管理器（如SQLiteSpy）来调试日期，因为日期不可读。不能使用SQLite日期函数（？）




• 使用简单的字符串格式，例如YYYYMMDDHHNNSS：转换是必需的，但在CPU上相对容易（无需扫描分隔符），数据是可读的。仍然无法使用SQLite日期功能。




• 执行其他操作。推荐做什么？

我已阅读 http://www.sqlite.org/lang_datefunc.html ，但是没有提到要使用什么数据类型，而不是在编程中正式上学，我不太在意在朱利安日期。为什么额外的转换？我会读这些值很多，所以字符串和TDateTime之间的任何其他转换增加了很大的代价。

解决方案

你可以使用SQLite支持的字符串格式之一，例如。 YYYY-MM-DD HH：MM：SS.SSS 。

它将和 YYYYMMDDHHNNSS 一样简单 - 您仍然不需要扫描分隔符，因为全部数字是固定的长度 - 您将获得SQLite日期功能支持。

如果您需要SQLite日期功能支持，我将使用该方法。

如果没有，我建议使用 REAL 值。您仍然可以将它们相互比较（较高的数字在时间上），并分别考虑日期和时间（分别在小数点之前和之后），而不转换为TDateTime。

I will be storing datetime values in an SQLite database (using Delphi and the DISqlite library). The nature of the db is such that it will never need to be transferred between computers or systems, so interoperability is not a constraint. My focus instead is on reading speed. The datetime field will be indexed and I will be searching on it a lot, as well as reading in thousands of datetime values in sequence.

Since SQLite does not have an explicit data type for datetime values, there are several options:

• use REAL data type and store Delphi's TDateTime values directly: fastest, no conversion from string on loading; impossible to debug dates using a db manager such as SQLiteSpy, since dates will not be human-readable. Cannot use SQLite date functions (?)

• use a simple string format, e.g. YYYYMMDDHHNNSS: conversion is required but relatively easy on the CPU (no need to scan for separators), data is human-readable. Still cannot use SQLite date functions.

• do something else. What's the recommended thing to do?

I have read http://www.sqlite.org/lang_datefunc.html but there's no mention of what data type to use, and, not being formally schooled in programming, I don't quite grok the focus on Julian dates. Why the additional conversion? I will be reading in these values a lot, so any additional conversions between strings and TDateTime adds a significant cost.

解决方案

You could use one of the SQLite supported string formats, eg. YYYY-MM-DD HH:MM:SS.SSS.

It would be just as easy as YYYYMMDDHHNNSS - you still wouldn't need to scan for separators, since all the numbers are fixed length - and you would get SQLite date function support.

If you need SQLite date function support, I would go with that method.

If not, I'd recommend using REAL values. You can still compare them to each other (higher numbers are later in time), and consider date and time separately (before and after the decimal point respectively) without converting to TDateTime.

这篇关于在SQLite数据库（Delphi）中存储datetime值的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！