mysql查询两次之间

I would like to query a mySQL table to pull out data between two times from January 10, like between 16:00 to 17:00. But in my database, I used to store date & time in strtotime formate and I should do it in Mysql.

Please help. Thank you!

Database image https://pasteboard.co/HVPMdqF.png

Sample Code:

SELECT * FROM table
WHERE loginTime between UNIX_TIMESTAMP('2019-01-10 16:00') AND UNIX_TIMESTAMP('2019-01-10 18:00')

But query not work.

loginTime type is varchar

That's where you're trouble starts. You should redesign the table an use a proper date/time type.

Meanwhile you essentially got two options.

1. Convert logintime to a Unix timestamp too.

   WHERE unix_timestamp(logintime) BETWEEN unix_timestamp('2019-01-10 16:00')
                                           AND unix_timestamp('2019-01-10 17:00')
   

2. Or compare strings.

   WHERE logintime BETWEEN '2019-01-10 16:00'
                           AND '2019-01-10 17:00'
   

Both require logintime to only store well formed date/time representations to work properly. For 1. there are some formats, that unix_timestamp() recognizes. For 2. it has to be exactly YYYY-MM-DD HH24:MI.