Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to extract year and month from date in PostgreSQL without using to_char() function?

Tags:

sql

postgresql

I want to select sql: SELECT "year-month" from table group by "year-month" AND order by date, where year-month - format for date "1978-01","1923-12". select to_char of couse work, but not "right" order:

to_char(timestamp_column, 'YYYY-MM')
like image 295
Bdfy Avatar asked Dec 25 '10 20:12

Bdfy


People also ask

How do you extract month and year from a date field?

Except the above formula, you can also apply this formula: =TEXT(A2, "mmm") & "-" & TEXT(A2, "yyyy"). 2. In above formulas, A2 indicates the date cell that you want to use, and the separator “-” is used to separate the month and year, you can change it to any other delimiters you need.

How do I extract year and month from timestamp?

You'll need to use the to_char function to extract the year-month from timestamp. Show activity on this post. select extract(year from timestmp) || '-' || extract(month from timestmp) from texmp1; Hope this helps.

What is to_char in Postgres?

The PostgreSQL to_char function converts a number or date to a string.


1 Answers

to_char(timestamp, 'YYYY-MM')

You say that the order is not "right", but I cannot see why it is wrong (at least until year 10000 comes around).

like image 115
yairchu Avatar answered Sep 30 '22 19:09

yairchu