How to compare mysql time type fields (accurate to hours, minutes and seconds)?

Question

I need to filter data based on the create_time field. Now the sql statement is as follows:

SELECT    * FROM    `sbhf_alarm` sa WHERE
unix_timestamp(sa.create_time) BETWEEN unix_timestamp('2016-05-25 14:07:56')
AND unix_timestamp('2017-05-25 14:07:56')

However, I found that this method has a bug, that is, when the upper limit time (arrow in the picture above) is too large, an error will be reported. As shown below:

The reason is that the unix_timestamp function converts time into seconds, and the time cannot be too large.

Question: Is there any other better way to filter time?
How is this problem generally solved? Please give me some advice, thank you very much...

Answer 1

UNIX_TIMESTAMP returns INTEGER instead of BIGINT, so it cannot exceed 2038 (again, 2106).

Why not try create_time BETWEEN '2016-05-25 14:07:56 AND '2017-05-25 14:07:56'?

In addition, if you are pursuing calculation convenience, it is recommended to set the time column type to BIGINT and store Unix timestamps in milliseconds, so that there will be no 2038 problem.

Answer 2

create_time BETWEEN '2016-05-25 14:07:56' AND '2017-05-25 14:07:56'

If you add milliseconds, be careful to use a colon:,

create_time BETWEEN '2016-05-25 14:07:56:000' AND '2017-05-25 14:07:56:000'