如何从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 .

我想选择sql:从table group中按“year-month”选择“year-month”，按日期排序，其中year-month格式为日期“1978-01”、“1923-12”。选择to_char，但不是“正确”顺序。

6 个解决方案

#1

date_part(text, timestamp)

date_part(文本、时间戳)

http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

#2

104

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

你说这个命令不是“正确的”，但是我不明白为什么它是错的(至少在10000年之前)。

#3

Use the date_trunc method to truncate off the day (or whatever else you want, e.g., week, year, day, etc..)

使用date_trunc方法截断一天(或您想要的任何其他内容，例如，周、年、日等)。

Example of grouping sales from orders by month:

按月对订单进行分组的示例:

select
  SUM(amount) as sales,
  date_trunc('month', created_at) as date
from orders
group by date
order by date DESC;

#4

You can truncate all information after the month using date_trunc(text, timestamp):

您可以使用date_trunc(文本，时间戳)截断当月后的所有信息:

select date_trunc('month',created_at)::date as date 
from orders 
order by date DESC;

#5

You Can use EXTRACT function pgSQL

可以使用提取函数pgSQL

EX- date = 1981-05-31
EXTRACT(MONTH FROM date)
it will Give 05

For more details PGSQL Date-Time

有关PGSQL日期-时间的详细信息

#6

1st Option

第一个选项

date_trunc('month', timestamp_column)::date

It will maintain the date format with all months starting at day one.

它将保持日期格式，所有月份从第一天开始。

Example:

例子:

2nd Option

第二个选项

to_char(timestamp_column, 'YYYY-MM')

This solution proposed by @yairchu worked fine in my case. I really wanted to discard 'day' info.

@yairchu提出的这个解决方案在我的案例中运行良好。我真的想放弃“日”信息。

#1