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

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

问题：此列中的数据同时包含linux时间戳和varchars：

1381148885

和

2012-09-17 22:13:17

如何查询此列中所有created_ts> 2013-10-01的记录

最佳答案

我选择通过检查破折号来区分字符串格式（“ 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

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



注意：我在％T中包括了hh：mm：ss部分。

如果没有提供时间分量，则时间分量假定为午夜（零）00:00:00，这取决于我们是否要考虑

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

OP查询具有大于比较的功能。我使用了大于或等于比较。

所有这些都可以根据要求进行调整。我们想知道，如果我们不小心，某些值将被“舍入”到前一个午夜，然后当我们进行大于比较时，我们真正得到的等同于>= '2013-10-02'。

我的偏好是使其更加明确。它使读者更容易理解查询的实际作用。



更新

我在LOCATE函数中将参数向后...要搜索的字符串应该是第一个参数，要搜索的字符串是第二个。在上面的查询中，该问题已得到纠正。