4

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.

8

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

6

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.