postgresql error : Multiple decimal points

Question

So I'm having this query:

SELECT 
    TO_CHAR(date_part('hour', created_at), 'YYYY-MM-DD HH24'),
    to_char(created_at, 'day') ",
    COUNT(*) AS "
FROM table  
GROUP BY 1,2
ORDER BY 1 DESC

When I execute the query I get this:

ERROR: multiple decimal points

Searching stackoverflow I found some recommendations here: How to format bigint field into a date in Postgresql? but I don't get why do I have to divide by 1000 and how this would apply in the case of the date_part function.

Answer 1

I assume created_at is a timestamp?.. I'm choosing from date_part(text, timestamp) and date_part(text, interval), if so date_part will return a double precision, to which you try to apply the mask 'YYYY-MM-DD HH24', eg:

v=# select date_part('hour', now());
 date_part
-----------
         9

and I don't see how you could possibly get year, month, day and hour from nine...

Yet I assume you wanted to apply the mask against truncated date to the hour precision, which is done with date_trunc(text, timestamp):

v=# select date_trunc('hour', now());
       date_trunc
------------------------
 2017-06-20 09:00:00+01
(1 row)

so now you can apply the time format:

v=# select to_char(date_trunc('hour', now()),'YYYY-MM-DD HH24');
    to_char
---------------
 2017-06-20 09
(1 row)

but if this is what you want, then you don't need to truncate time at all:

v=# select to_char(now(),'YYYY-MM-DD HH24');
    to_char
---------------
 2017-06-20 09
(1 row)

https://www.postgresql.org/docs/current/static/functions-datetime.html