Retrieve data from MySQL table stored as milliseconds-CodePudding

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

CodePudding user response：

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()