PostgreSQL Alternative methods to convert timestamp

Question

I'm new to PostgreSQL and I'm wondering if there is a faster/easier way to convert a timestamp to YY or YYYYMM format?

**substring**

select count(1), substring(text(id_timestamp),0,8)  
from dual
where  id_timestamp >= (now()- '3 month'::INTERVAL)
group by substring(text(id_timestamp),0,8) 


**to_char**

select count(1),to_char(id_timestamp,'YYYY-MM') 
from dual
where  id_timestamp >= (now()- '3 month'::INTERVAL)
group by to_char(id_timestamp,'YYYY-MM') 

Year:

select count(1),to_char(id_timestamp,'YYYY') 
from dual
where  id_timestamp >= (now()- '3 month'::INTERVAL)
group by to_char(id_timestamp,'YYYY')

Clodoaldo Neto · Accepted Answer · 2014-12-27 23:12:45Z

1

to_char is how you do it. To not have to repeat the expressions in the select list it is possible to refer to them by their position in the list:

select count(*), to_char(id_timestamp,'YYYY-MM') 
from t
group by 2
order by 2

There is no dual in Postgresql.

edited Dec 27, 2014 at 23:12

answered Dec 27, 2014 at 23:05

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL Alternative methods to convert timestamp

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related