Get truncked data from a table - postgresSQL-CodePudding

I want to get truncked data over the last month. My time is in unix timestamps and I need to get data from last 30 days for each specific day. The data is in the following form:

{
"id":"648637",
"exchange_name":"BYBIT",
"exchange_icon_url":"https://cryptowisdom.com.au/wp-content/uploads/2022/01/Bybit-colored-logo.png",
"trade_time":"1675262081986",
"price_in_quote_asset":23057.5,
"price_in_usd":1,
"trade_value":60180.075,
"base_asset_icon":"https://assets.coingecko.com/coins/images/1/large/bitcoin.png?1547033579",
"qty":2.61,
"quoteqty":60180.075,
"is_buyer_maker":true,
"pair":"BTCUSDT",
"base_asset_trade":"BTC",
"quote_asset_trade":"USDT"
}

I need to truncate data based on trade_time How do I write the query?

CodePudding user response：

The secret sauce is the date_trunc function, which takes a timestamp with time zone and truncates it to a specific precision (hour, day, week, etc). You can then group based on this value.

In your case we need to convert these ~~unix timestamps~~ javascript style timestamps to timestamp with time zone first, which we can do with to_timestamp, but it's still a fairly simple query.

SELECT
    date_trunc('day', to_timestamp(trade_time / 1000.0)),
    COUNT(1)
FROM pings_raw
GROUP BY date_trunc('day', to_timestamp(trade_time / 1000.0))

Another approach would be to leave everything as numbers, which might be marginally faster, though I find it less readable

SELECT
    (trade_time/(1000*60*60*24))::int * (1000*60*60*24),
    COUNT(1)
FROM pings_raw
GROUP BY (trade_time/(1000*60*60*24))::int