Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    44

    Unanswered: Bigint '1403507102948' to human readable timestamp

    Hi

    We have strange timestamps in our database. I don't know how to convert to real timestamp. It seems not UNIX time stamp.

    Please help me to get real timestamp

    Code:
    mysql> select FROM_UNIXTIME(created), FROM_UNIXTIME(modified), created, modified from spt_work_item order by created desc LIMIT 20 ;
    +------------------------+-------------------------+---------------+---------------+
    | FROM_UNIXTIME(created) | FROM_UNIXTIME(modified) | created       | modified      |
    +------------------------+-------------------------+---------------+---------------+
    | NULL                   | NULL                    | 1403507102948 | 1403507102992 |
    | NULL                   | NULL                    | 1403504091976 | 1403504091984 |
    | NULL                   | NULL                    | 1403504084851 | 1403504084859 |
    | NULL                   | NULL                    | 1403504077332 | 1403504077353 |
    | NULL                   | NULL                    | 1403266753149 | 1403266753188 |
    | NULL                   | NULL                    | 1403178952977 | 1403178953009 |
    | NULL                   | NULL                    | 1402902303182 | 1402902303271 |
    | NULL                   | NULL                    | 1402673873827 | 1402673873865 |
    | NULL                   | NULL                    | 1401948308117 | 1401948308435 |
    | NULL                   | NULL                    | 1401863340648 | 1401863340654 |
    | NULL                   | NULL                    | 1401863258544 | 1401863258550 |
    | NULL                   | NULL                    | 1401815102449 | 1401815102557 |
    | NULL                   | NULL                    | 1401797102762 | 1401797102932 |
    | NULL                   | NULL                    | 1401795501881 | 1401795501989 |
    | NULL                   | NULL                    | 1401789912907 | 1401789913535 |
    | NULL                   | NULL                    | 1401789902469 | 1401789903234 |
    | NULL                   | NULL                    | 1401789333393 | 1401789333400 |
    | NULL                   | NULL                    | 1401789313894 | 1401789313901 |
    | NULL                   | NULL                    | 1401789301646 | 1401789301652 |
    | NULL                   | NULL                    | 1401789275740 | 1401789275746 |
    +------------------------+-------------------------+---------------+---------------+
    20 rows in set (0.00 sec)
    Code:
    mysql> desc spt_work_item
        -> ;
    +----------------------+---------------+------+-----+---------+-------+
    | Field                | Type          | Null | Key | Default | Extra |
    +----------------------+---------------+------+-----+---------+-------+
    | id                   | varchar(128)  | NO   | PRI | NULL    |       |
    | created              | bigint(20)    | YES  |     | NULL    |       |
    | modified             | bigint(20)    | YES  |     | NULL    |       |
    | owner                | varchar(128)  | YES  | MUL | NULL    |       |
    | assigned_scope       | varchar(128)  | YES  | MUL | NULL    |       |
    | assigned_scope_path  | varchar(450)  | YES  | MUL | NULL    |       |
    | name                 | varchar(255)  | YES  | MUL | NULL    |       |
    | description          | varchar(1024) | YES  |     | NULL    |       |
    | handler              | varchar(255)  | YES  |     | NULL    |       |
    | renderer             | varchar(255)  | YES  |     | NULL    |       |
    | target_class         | varchar(255)  | YES  |     | NULL    |       |
    | target_id            | varchar(255)  | YES  | MUL | NULL    |       |
    | target_name          | varchar(255)  | YES  |     | NULL    |       |
    | type                 | varchar(255)  | YES  | MUL | NULL    |       |
    | state                | varchar(255)  | YES  |     | NULL    |       |
    | severity             | varchar(255)  | YES  |     | NULL    |       |
    | requester            | varchar(128)  | YES  | MUL | NULL    |       |
    | completion_comments  | longtext      | YES  |     | NULL    |       |
    | notification         | bigint(20)    | YES  |     | NULL    |       |
    | expiration           | bigint(20)    | YES  |     | NULL    |       |
    | wake_up_date         | bigint(20)    | YES  |     | NULL    |       |
    | reminders            | int(11)       | YES  |     | NULL    |       |
    | escalation_count     | int(11)       | YES  |     | NULL    |       |
    | notification_config  | longtext      | YES  |     | NULL    |       |
    | workflow_case        | varchar(128)  | YES  | MUL | NULL    |       |
    | attributes           | longtext      | YES  |     | NULL    |       |
    | owner_history        | longtext      | YES  |     | NULL    |       |
    | certification        | varchar(255)  | YES  |     | NULL    |       |
    | certification_entity | varchar(255)  | YES  |     | NULL    |       |
    | certification_item   | varchar(255)  | YES  |     | NULL    |       |
    | identity_request_id  | varchar(128)  | YES  | MUL | NULL    |       |
    | assignee             | varchar(128)  | YES  | MUL | NULL    |       |
    | certification_ref_id | varchar(128)  | YES  | MUL | NULL    |       |
    | idx                  | int(11)       | YES  |     | NULL    |       |
    +----------------------+---------------+------+-----+---------+-------+
    34 rows in set (0.00 sec)

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It may be miliseconds from 1970-01-01.

    Unix timestamp is seconds since '1970-01-01 00:00:00' UTC, according to manual.

    So, how about FROM_UNIXTIME(created / 1000)?
    or FROM_UNIXTIME(created DIV 1000)?
    Last edited by tonkuma; 06-25-14 at 05:30. Reason: Add second and following sentences.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a radical thought might suggest you investigate the code which is inserting the rows....

    however I suspect tonkuma is correct

    Code:
    select from_unixtime(created / 1000)...
    returns... 2014-06-23 08:05:03, which could well be a reasonable date


    far better to understand what is going into the db, and why than try to work out how to extract it
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2012
    Posts
    44
    thank you guys.

    Code:
    mysql> SELECT FROM_UNIXTIME(created DIV 1000), created from spt_work_item order by created desc limit 10;
    +---------------------------------+---------------+
    | FROM_UNIXTIME(created DIV 1000) | created       |
    +---------------------------------+---------------+
    | 2014-06-25 10:34:50             | 1403685290550 |
    | 2014-06-25 10:33:17             | 1403685197321 |
    | 2014-06-25 10:05:00             | 1403683500557 |
    | 2014-06-23 09:05:02             | 1403507102948 |
    | 2014-06-23 08:14:51             | 1403504091976 |
    | 2014-06-23 08:14:44             | 1403504084851 |
    | 2014-06-23 08:14:37             | 1403504077332 |
    | 2014-06-20 14:19:13             | 1403266753149 |
    | 2014-06-19 13:55:52             | 1403178952977 |
    | 2014-06-16 09:05:03             | 1402902303182 |
    +---------------------------------+---------------+
    10 rows in set (0.00 sec)
    
    mysql>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •