What function to use with this timestamp?

Question

I have timestamp (stored as BIGINT) format I cannot turn into a human-readable format:

1462975819250

It points to a date in 2016-may-11th. However function to_timestamp() turns this into:

48329-11-10 13:00:49.999872+01

I'd like to create a VIEW where I could display these values, don't know what function to use.

The field is written by a Java program (EclipseLink I presume). I don't have the source-code of the program.

Answer 1

to_timestamp() expects the parameter to be given in seconds. Your value is in miliseconds. Just divide it by 1000:

SELECT to_timestamp(1462975819.250);

gives 2016-05-11 16:10:19.25+02

Answer 2

Seems like you are storing milliseconds since 1970-01-01 00:00:00 so Adam's answer is right. Another way would be to use interval addition to the 'epoch' timestamp (which is 1970-01-01 00:00:00):

select timestamptz 'epoch' + 1462975819250 * interval '1 millisecond' 
       as my_timestamp ;

Tested:

test=# select timestamptz 'epoch' + 1462975819250 * interval '1 millisecond' 
              as my_timestamp ;
       my_timestamp        
---------------------------
 2016-05-11 15:10:19.25+01
(1 row)

Adams:

test=# select to_timestamp(1462975819250 / 1000.0) ;
       to_timestamp        
---------------------------
 2016-05-11 15:10:19.25+01
(1 row)

the 'epoch' timestamp:

test=# select timestamptz 'epoch' as the_start_of_times;
   the_start_of_times   
------------------------
 1970-01-01 01:00:00+01
(1 row)

(The 1-hour diff and the +1 are because my local settings are in UTC+1.)

And from the related Jaca docs Class Timestamp:

getTime

public long getTime()

Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT represented by this Timestamp object.

Overrides:
getTime in class Date

Returns:
the number of milliseconds since January 1, 1970, 00:00:00 GMT represented by this date.

See Also:
setTime(long)