How to pour / put float value in datetime
For minimal case
If min(ticktime)::TIMESTAMP
- 2014-01-02 01:14:45.5
, I want to convert it to2014-01-02 01:14:45
For max. case
If max(ticktime)::TIMESTAMP
- 2014-01-02 01:14:45.5
, I want to convert it to2014-01-02 01:14:46
SELECT DISTINCT ON (1) generate_series ( min(ticktime)::TIMESTAMP, max(ticktime)::TIMESTAMP, '1 second'::interval) AS ticktime
FROM cffexes
source to share
You can just pass the result a second time timestamp
, like:
SELECT DISTINCT ON (1) generate_series (
min(ticktime)::TIMESTAMP(0),
max(ticktime)::TIMESTAMP(0)+1,
'1 second'::interval) AS ticktime
FROM cffexes;
If you want to use gender / time other than seconds use date_trunc()
.
Of course, open this whole hole so small that if max(ticktime)
EXACTLY even a second, you will be generating too much. So you can try this, but understand that floating point values are notoriously unfriendly to equality.
SELECT DISTINCT ON (1) generate_series (
min(ticktime)::TIMESTAMP(0),
max(ticktime)::TIMESTAMP(0) + (max(ticktime)::TIMESTAMP(0)=max(ticktime)::TIMESTAMP)::int,
'1 second'::interval) AS ticktime
FROM cffexes;
source to share
I just add date_trunc to the beginning of generate_series,
And it works for mine
SELECT DISTINCT ON (1) generate_series
(
date_trunc('second', min(ticktime)::TIMESTAMP),
max(ticktime)::TIMESTAMP,
'1 #{frequence}'::interval
) AS ticktime FROM #{market} WHERE product_type ='#{product_type}' AND contract_month = '#{contract_month}'::timestamp
source to share