0

I have a MySQL database. There I have a table which has a column called date. The type of that column is String. That column contains a timestamp value (i.e. 1370842140205).

How can I select that value as a date? - 10.6.2013 5.29.00

Aksiom
  • 1,565
  • 4
  • 25
  • 39
  • What kind of timestamp is it? It is not a unix timestamp. – juergen d Jun 12 '13 at 13:49
  • I saved that timestamp in my database from the Java function `System.currentTimeMillis()`. But I saved it as a String (Varchar) in my database. – Aksiom Jun 12 '13 at 13:51
  • That's no use. `System.currentTimeMillis` is - well - system dependant. Change the clock on your system and you'll get a different value... You can't get that as a date reliably. – Sebastian Jun 12 '13 at 13:52
  • @Sebastian I don't understan what you are trying to say? I used `System.currentTimeMillis` to get the current time in timestamp format, right? I saved that value in my database as a String. Now i want to retrive that timestamp and convert it into a date. I think that should be possible? – Aksiom Jun 12 '13 at 13:56
  • That's just not the way to do it. Use unix-style timestamps, that's the normal way to store date and time in databases. Take a look here: http://stackoverflow.com/questions/732034/getting-unixtime-in-java – Sebastian Jun 12 '13 at 13:58

1 Answers1

2

Use FROM_UNIXTIME

SELECT FROM_UNIXTIME(`date` / 1000.0)
from your_table

and remove the milli seconds from the timestamp with /1000.

SQLFiddle demo

Community
  • 1
  • 1
juergen d
  • 201,996
  • 37
  • 293
  • 362