SQL 如何在不使用 to_char() 函数的情况下从 PostgreSQL 中的日期中提取年和月？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4531577/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extract year and month from date in PostgreSQL without using to_char() function?
提问by Bdfy
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:
SELECT "year-month" from table group by "year-month" AND order by date，其中年月 - 日期格式为“1978-01”、“1923-12”。
选择 to_char of couse work，但不是“正确”的顺序：
to_char(timestamp_column, 'YYYY-MM')
采纳答案by MK.
date_part(text, timestamp)
e.g.
例如
date_part('month', timestamp '2001-02-16 20:38:40'),
date_part('year', timestamp '2001-02-16 20:38:40')
http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html
http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html
回答by yairchu
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 年到来之前）。
回答by Gerry Shaw
Use the date_truncmethod 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;
回答by Aya
You can truncate all information after the month using date_trunc(text, timestamp):
您可以使用date_trunc(text, timestamp)以下方法截断月份后的所有信息：
select date_trunc('month',created_at)::date as date
from orders
order by date DESC;
Example:例子：
Input:输入：
created_at = '2019-12-16 18:28:13'
Output 1:
输出 1：
date_trunc('day',created_at)
// 2019-12-16 00:00:00
Output 2:
输出 2：
date_trunc('day',created_at)::date
// 2019-12-16
Output 3:
输出 3：
date_trunc('month',created_at)::date
// 2019-12-01
Output 4:
输出 4：
date_trunc('year',created_at)::date
// 2019-01-01
回答by Luis Martins
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 提出的这个解决方案在我的情况下工作得很好。我真的很想丢弃“日”信息。
回答by Singhak
You Can use EXTRACT function pgSQL
您可以使用 EXTRACT 函数 pgSQL
EX- date = 1981-05-31
EXTRACT(MONTH FROM date)
it will Give 05
For more details PGSQL Date-Time
有关更多详细信息 PGSQL 日期时间
回答by Anurag Bhardwaj
It is working for "greater than" functions not for less than.
它适用于“大于”函数而不是小于。
For example:
例如：
select date_part('year',txndt)
from "table_name"
where date_part('year',txndt) > '2000' limit 10;
is working fine.
工作正常。
but for
但对于
select date_part('year',txndt)
from "table_name"
where date_part('year',txndt) < '2000' limit 10;
I am getting error.
我收到错误。
