How to sort unix timestamp in mysql

Question

I am trying to retrieve some data using the below query. I need to add one more table in the below query, 'request_meta', and have to sort using its column 'meta-value' which has data including texts, and unix timestamps.

I need to get these result in the chronological order of the timestamps in column 'meta-value' . Can anyone help me out?

 SELECT rt.taxonomy_id,rt.iteration,ra.*,
  FROM request_taxonomy rt 
  inner join request_aspects ra ON ra.aspect_id = rt.request_aspects_id
  WHERE rt.requests_id = 17; 

  request_meta table structure as follows:
  ----------------------------------------    
  meta_id - Pri
  requests_id  - bigint 
  request_taxonomy_id  - bigint 
  meta-value  - varchar

Sample data in request_meta table . Third row is the date stored. The data from other tables needs to be sorted based on the ascending order of dates.

requests_id meta_value request_taxonomy_id
17           8              925
17     Customer Cluster     926
17      1446792318          966
17      1446103569          967

Do you mean that `meta-value` contains text with a time-stamp embedded in the text? Can you please show some sample data of the column? — Some programmer dude, Nov 06 '15 at 07:48

score 0 · Answer 1 · edited May 23 '17 at 12:03

If I get this right, you have to filter for rows where meta_value actually is a timestamp and not something else.

So you should check for it being numeric. How that can be done is explained here: Detect if value is number in MySQL

You could then filter false positives by setting a minimal string length to ignore values like 8 in the meta_value column.

So, just referring to the meta_value that could look like:

SELECT *
FROM mytable
WHERE concat('',meta_value* 1) = meta_value AND  LENGTH(meta_value)>=10 
ORDER BY meta_value;

If you have older timestamps to manage you might want to change the string length condition.

But I doubt that this is a performant way to solve your problem. To address the issue properly you should think changing your db structure in a way that stores timestamp as numeric values.

How to sort unix timestamp in mysql

1 Answers1