How to extract date from a timestamp who data type is int

Question

The existing database's table has a column "dateline" with int data type. It stores timestamp. How can I extract date from it.

Dateline (int)
_____________
1314182844
1298122381
1298122956

Advise on how can I extract the date part from this in the following query.

select * from TableName where Dateline between '2013-10-01' and '2013-10-31'

I tried using cast, dateformat, convert, but nothing gave the desired result.

P.S. PLEASE NOTE THE DATA TYPE STORAGE IS INTEGER..

This may give you a clue: [epoch time and MySQL query](http://stackoverflow.com/questions/7825739/epoch-time-and-mysql-query) — atomman, Oct 21 '13 at 15:04
Look at this post : http://stackoverflow.com/questions/6267564/convert-unix-timestamp-into-human-readable-date-using-mysql/6267625#6267625 — kmas, Oct 21 '13 at 15:05

score 3 · Accepted Answer · answered Oct 21 '13 at 15:04

3

You can use FROM_UNIXTIME(Dateline).

See more information here.

As atomman mentions in the comments, you should use DATE(FROM_UNIXTIME(Dateline)) if you only want the date part.

answered Oct 21 '13 at 15:04

Mr47

1

Would it be `DATE(FROM_UNIXTIME(timestampcolumn))`, since he is only cares about the date. – atomman Oct 21 '13 at 15:05
Well, the datatype is int and the above code does not work...The only value I get is 1970-01-01 for whatever timestamp it is – Siri Oct 21 '13 at 15:11
It is working for other tables with same int data type. Im not sure why it is giving garbage value only for this table :( – Siri Oct 21 '13 at 15:16
Got it..Thanks..Few of the initial values of the database are garbage values. sorry for the confusion. thanks again for all inputs – Siri Oct 21 '13 at 15:20
@user1808995 Glad to have been of assistance :) – Mr47 Oct 21 '13 at 15:21

1 Answers1