如何从PostgreSQL中提取年和月数据?

时间:2022-05-27 04:34:47

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


26  

date_part(text, timestamp)

date_part(文本、时间戳)

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

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


24  

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


11  

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


4  

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


3  

1st Option

第一个选项

date_trunc('month', timestamp_column)::date

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

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

Example:

例子:

2016-08-01
2016-09-01
2016-10-01
2016-11-01
2016-12-01
2017-01-01

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


26  

date_part(text, timestamp)

date_part(文本、时间戳)

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

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


24  

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


11  

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


4  

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


3  

1st Option

第一个选项

date_trunc('month', timestamp_column)::date

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

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

Example:

例子:

2016-08-01
2016-09-01
2016-10-01
2016-11-01
2016-12-01
2017-01-01

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提出的这个解决方案在我的案例中运行良好。我真的想放弃“日”信息。