MySQL varchar timestamp 列存储两种不同的方式

Question

我有一个包含设备数据的表，列之一 created_ts -> varchar(30)

问题:此列中的数据同时包含 linux 时间戳和 varchars，例如:

1381148885

和

2012-09-17 22:13:17

我如何查询此列以获取 created_ts > 2013-10-01 的所有记录

Answer 1

我会选择通过检查短划线字符来区分字符串格式('YYYY-MM-DD' 或 unix 时间戳整数)。

我会考虑使用适当的转换将这两种格式显式转换为 DATE 数据类型。我会将生成的 DATE 值与日期文字进行比较。

像这样:

 WHERE IF(LOCATE('-',t.created_ts,5),              -- which format (yyyy-mm-dd or integer)
          STR_TO_DATE(t.created_ts,'%Y-%m-%d %T'), -- convert yyyy-mm-dd string to date
          FROM_UNIXTIME(t.created_ts)              -- convert string as integer to date
       ) >= '2013-10-01'                           -- compare to date literal

另一种选择是将字符串列和日期文字转换为整数值，并进行整数比较。 (同样，字符串列有两种不同的转换，具体取决于格式。)

---

注意:我在转换中包含了 hh:mm:ss 部分和 %T。

当没有提供时间分量时，时间分量被假定为午夜(零)00:00:00，这取决于我们是否要考虑

'2013-10-01 07:34:55' > '2013-10-01 00:00:00'

OP查询有大于比较。我使用了大于或等于比较。

这一切都可以根据要求进行调整。我们要注意，如果我们不小心，一些值会“向下舍入”到前一个午夜，然后当我们进行大于比较时，我们真正得到的相当于 >= '2013-10-02'.

我的偏好是让它更明确。它使读者更容易理解查询实际在做什么。

---

更新

我在 LOCATE 函数中的参数倒过来了……要搜索的字符串应该是第一个参数，要搜索的字符串是第二个。这已在上面的查询中得到纠正。