Akash Aggarwal Akash Aggarwal - 1 month ago 6
SQL Question

MySQL TIMESTAMP behaving weirdly

In my chat application, I use

System.currentTimeMillis()/1000
on the android app to send the time the message was created at to my MySQL server, without the milliseconds part. Now the strange part is, my table has two records with the same value of milliseconds. Even stranger is that when I convert the milliseconds to readable time, the values are totally different!

Here is the output of two different queries:

SELECT max(UNIX_TIMESTAMP(created_at)), created_at, message FROM messages WHERE mtype = 0 AND groups_id = 1;

max(UNIX_TIMESTAMP(created_at)) | created_at | message |
+--------------------------------+---------------------+---------+
| 1464096148 | 2016-04-25 13:57:18 | The


SELECT UNIX_TIMESTAMP(created_at), created_at, MESSAGE FROM messages WHERE mtype = 0 AND groups_id = 1 ORDER BY created_at DESC LIMIT 1;

max(UNIX_TIMESTAMP(created_at)) | created_at | message |
+--------------------------------+---------------------+---------+
| 1464096148 | 2016-05-24 18:52:28 | test


The output of the last query is what I remember sending from my client app.

Answer

Nothing is acting weird, it acting exactly as it should be.

When you have more then one record, and you are using an aggregation function like MAX()/MIN()/COUNT()... , if the other columns without the aggregation function has different values, one of them will be picked(not sure by what order, I think randomly) .

So if you would like them to return the same thing, this is the query you should use:

SELECT UNIX_TIMESTAMP(created_at), created_at, message FROM messages 
WHERE UNIX_TIMESTAMP(created_at) =(SELECT  max(UNIX_TIMESTAMP(created_at))
                                   FROM messages WHERE mtype = 0 AND groups_id = 1)
  and mtype = 0 AND groups_id = 1;

This will make sure to pick the record, where the timestamp equals to the maximum timestamp, this will make sure you pick the correct record. When dealing with aggregation functions, you have to specify to each column which one of the values you want.