CODEWITHSUNDEEP
sqlpostgresql
Dynamiite
Dynamiite

Reputation: 1499

PostgreSQL Alternative methods to convert timestamp

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')

Upvotes: 0

Views: 335

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125414

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.

Upvotes: 1

Related Questions