3

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.

Mewtwo
  • 1,231
  • 2
  • 18
  • 38

1 Answers1

4

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

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Thanks for the comment! I know that the SQL code above seems weird. Actually I'm using a BI tool that uses a markup that afterwards translates this markup into SQL. So this was the "translation". Unfortunately, I can't avoid the mask or the to_char(). Given that, my initial goal is to group some events by the day (Monday, Tuesday etc) and time of the day they occur (1 am, 2 am , 3 am etc). Truncating to hour will keep all the information about month and year which I don't want. Is there a way to overcome this ? – Mewtwo Jun 20 '17 at 09:27
  • Actually I solved my problem using extract. I'm going to accept your answer as it helped very much – Mewtwo Jun 20 '17 at 09:38