0

I have a .dat file that represents a Table in a database and has a particular column for timestamp values. Now the values are stored in the following way: -

978302039

I'm required to copy all the elements into another table, but I'm not sure if the column containing these values should be of int or timestamp. As far as I know, we store timestamp values in the following format: -

1000:09:12 00:00:00

But these values stored here don't look anything like that. Is there any internal representation of timestamp values?

Auro
  • 1,578
  • 3
  • 31
  • 62

2 Answers2

0

Per MySQL Documentation

MySQL recognizes TIMESTAMP values in these formats:

As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

With that, the value you have posted looks like a invalid timestamp to me.

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • But the numbers in my file supposedly representing `timestamp` values are small in the sense that it has less number of digits. Could it be the representation of time in milliseconds? – Auro Sep 20 '16 at 15:21
0

978302039 looks like a unix timestamp in seconds. You can convert it to MySQLs DATETIME with the FROM_UNIXTIME() function.

SELECT FROM_UNIXTIME(978302039)

will return something like

2000-12-31 22:33:59

The exact value depends on the timezone configuration of your server.

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53