Note that the milliseconds must be converted to seconds before they can be applied as an interval to the epoch (01/01/1970).
Table "public.candle_l"
Column | Type | Modifiers
--------+--------------+-----------
ticker | character(8) | not null
minute | bigint | not null
open | numeric | not null
high | numeric | not null
low | numeric | not null
close | numeric | not null
volume | bigint | not null
SELECT ticker, minute, '1970-01-01 00:00:00 GMT'::timestamp + ((minute/1000)::text)::interval, close FROM candle_l where ticker='BARC' ORDER BY 2;
ticker | minute | ?column? | close
----------+---------------+---------------------+---------
BARC | 1306220400000 | 2011-05-24 07:00:00 | 266.7
BARC | 1306220460000 | 2011-05-24 07:01:00 | 266.8
BARC | 1306220520000 | 2011-05-24 07:02:00 | 266
BARC | 1306220580000 | 2011-05-24 07:03:00 | 265.95
BARC | 1306220640000 | 2011-05-24 07:04:00 | 266.25
BARC | 1306220700000 | 2011-05-24 07:05:00 | 266.6
BARC | 1306220760000 | 2011-05-24 07:06:00 | 267.15
Column | Type | Modifiers
--------+--------------+-----------
ticker | character(8) | not null
minute | bigint | not null
open | numeric | not null
high | numeric | not null
low | numeric | not null
close | numeric | not null
volume | bigint | not null
SELECT ticker, minute, '1970-01-01 00:00:00 GMT'::timestamp + ((minute/1000)::text)::interval, close FROM candle_l where ticker='BARC' ORDER BY 2;
ticker | minute | ?column? | close
----------+---------------+---------------------+---------
BARC | 1306220400000 | 2011-05-24 07:00:00 | 266.7
BARC | 1306220460000 | 2011-05-24 07:01:00 | 266.8
BARC | 1306220520000 | 2011-05-24 07:02:00 | 266
BARC | 1306220580000 | 2011-05-24 07:03:00 | 265.95
BARC | 1306220640000 | 2011-05-24 07:04:00 | 266.25
BARC | 1306220700000 | 2011-05-24 07:05:00 | 266.6
BARC | 1306220760000 | 2011-05-24 07:06:00 | 267.15