c# - 将 DateTime.Ticks 值转换为 SQL 日期的 MySQL 脚本

Question

我有一个旧的 MySQL 数据库，我将日期存储为 C# DateTime.Ticks .

现在我想将该旧数据库转换为新结构，以用于需要日期字段的 PHP 应用程序。如何将 DateTime.Ticks 转换为 MySQL 日期？

我正在寻找以下格式的内容YYYY-MM-DD HH:mm:ss:

SELECT someconversion(olddate) as newDate FROM table;

先谢谢你

Answer 1

自 0001-01-01 00:00:00 UTC 以来，Dotnet Ticks 以 100ns(每秒一千万)为单位存储在 64 位整数中。

MySQL Days (as used in FROM_DAYS())从 0000-01-01 开始计算经过的天数。

例如 2016-01-09 00:00:00 在 Ticks 中是 635879124000000000 或者，为了便于阅读

  635 879 124 000 000 000

因此，您的 oldate 列数据类型可能是 MySQL 中的 BIGINT 或 DOUBLE。要么，要么它是一个文本字符串。

需要知道的是，UNIX 纪元 1970-01-01 00:00:00 UTC 具有此 Ticks 值:621355968000000000。这是最终的神奇数字。

无论如何，这是在 MySQL 中转换 Ticks 的方法。我们将转换为 UNIX 时间戳，然后转换为 DATETIME 列。

SELECT FROM_UNIXTIME((yourcolumn/10000000.0) - (62135596800.0))

但是，事情是这样的。此 FROM_UNIXTIME() 的输出隐式转换为您的本地时间。您可能希望在存储此数据时保留 UTC 时间。

有两种方法可以做到这一点。一种是使用 TIMESTAMP 数据类型来存储这些转换后的时间。另一种是在执行此转换操作之前将 MySQL session 时间戳设置为“UTC”，如下所示:

  SET time_zone = 'UTC';

这是做同样事情的另一种方法，但不依赖于使用 Unix 时间戳。 (这些时间戳仅在 1970 年到 2038 年期间有效。)这取决于将刻度转换为天，然后将余数转换为秒。它将报价直接转换为 DATETIME 数据类型。

select FROM_DAYS(365+(yourcolumn / 864000000000))
   + INTERVAL (yourcolumn % 864000000000) / 10000000 SECOND

常量 864000000000 (864 000 000 000) 是一天中的刻度数。

分解:

1. yourcolumn/864000000000 是从 0001-01-01 开始的天数
2. FROM_DAYS(365+(yourcolumn/864000000000)) 是日期。
3. (yourcolumn % 864000000000)/10000000 是步骤 1 中除法的余数，以秒为单位。
4. FROM_DAYS(date) + INTERVAL seconds SECOND 获取完整时间戳。