CODEWITHSUNDEEP
mysqlunix-timestamp
赣西狠人
赣西狠人

Reputation: 342

select data by unix timestamp

Below are the table I've created and its records:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  created_at datetime
);

INSERT INTO students VALUES (1, 'Ryan', 'M', '2012-11-03 00:00:00');
INSERT INTO students VALUES (2, 'Joanna', 'F', '2013-11-03 00:00:00');

To fetch the records by time, I use the following SQL statement:

SELECT * FROM students WHERE created_at > 1620489600000;

and both records can be returned, which confuses me because 1620489600000 (Sat May 08 2021 16:00:00 GMT+0000) should be a timestamp way later than the create_at fields of both records.

Indeed I know this can also be achieved by specifying a time formatted as 2012-11-03 00:00:00, but I just wonder:

Upvotes: 1

Views: 389

Answers (1)

juergen d
juergen d

Reputation: 204766

To compare two different data types MySQL automatically converts one of them to the other. In this case it tries to make numbers of the dates. What is the result? See here

The date gets converted to

20121103000000

which is bigger than

1620489600000

So the result in your WHERE clause is true and returns all records.

If you need to go from a Unix Timestamp you could use this:

WHERE created_at > FROM_UNIXTIME(1620489600000 / 1000)

Notice that I divided by 1000 because Unix time has to be in seconds and not milli seconds.

Upvotes: 2

Related Questions