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.

what do you try to achieve?.. what is expected result?..

Vao Tsun
– Vao Tsun

2017-06-20 08:06:42 +00:00
Commented Jun 20, 2017 at 8:06 — Vao Tsun
– Vao Tsun, Commented Jun 20, 2017 at 8:06

Vao Tsun · Accepted Answer · 2017-06-20 08:14:17Z

5

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

answered Jun 20, 2017 at 8:14

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mewtwo Over a year ago

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 Over a year ago

Actually I solved my problem using extract. I'm going to accept your answer as it helped very much

Collectives™ on Stack Overflow

postgresql error : Multiple decimal points

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related