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

1 answer

  • answered 2021-10-12 15:54 RiggsFolly

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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum