Retrieve data from MySQL table stored as milliseconds

I have a field named premium_end_date, and I run a query to retrieve a date that is smaller than premium_end_daten, the field records are stored as milliseconds:

mysql> SELECT id_prem, premium_end_date FROM ads_premium LIMIT 1;
+---------+------------------+
| id_prem | premium_end_date |
+---------+------------------+
|       1 |       1633967568 |
+---------+------------------+
1 row in set (0.00 sec)

The table looks like:

mysql> describe ads_premium;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id_prem            | int unsigned | NO   | PRI | NULL    | auto_increment |
| premium_views      | bigint       | YES  |     | NULL    |                |
| premium_start_date | bigint       | YES  |     | NULL    |                |
| premium_end_date   | bigint       | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+
10 rows in set (0.03 sec)

I run this query:

/** code here */
WHERE premium_end_date >= CURDATE()
/** code here */

But as the record is in milliseconds, it does not return anything, and I do not want to change the field from bigint to another one, as I have a lot of data inside

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

That looks like a straight forward unix timestamp to me.

If you run,

SELECT FROM_UNIXTIME(1633967568);

Do you get a date like 2021-10-11 15:52:48

So you can select using

WHERE FROM_UNIXTIME(premium_end_date) >= CURRENT_TIMESTAMP()

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0