blocks|key|3215721|text|date_part(text,+timestamp)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3215722|例如：|unstyled|3215723|date_part('month',+timestamp+'2001-02-16+20:38:40'),
date_part('year',+timestamp+'2001-02-16+20:38:40')+|3215724|http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html|offset|length|3215725|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|1Y|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|V|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|W|8|@]|9|@$K|X|L|Y|1|Z]]|A|$]]|$1|M|3|-4|5|F|7|10|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|J]]]]

<pre><code>date_part(text, timestamp)
</code></pre>

e.g.

<pre><code>date_part('month', timestamp '2001-02-16 20:38:40'),
date_part('year', timestamp '2001-02-16 20:38:40') 
</code></pre>

<a href="http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html" rel="noreferrer">http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html</a>

blocks|key|3002495|text|to_char(timestamp,+'YYYY-MM')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3002496|您说顺序不“正确”，但我不明白为什么它是错误的(至少在公元10000年到来之前)。|unstyled|3002497|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>to_char(timestamp, 'YYYY-MM')
</code></pre>

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

blocks|key|286664|text|使用date_trunc方法截断日期(或您想要的任何其他内容，例如，周、年、日等)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|286665|按月对订单进行销售分组的示例：|286666|select
++SUM(amount)+as+sales,
++date_trunc('month',+created_at)+as+date
from+orders
group+by+date
order+by+date+DESC;|code-block|syntax|javascript|286667|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC^0|2|A|2|A|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|6|7|10|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|11|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

Use the <a href="http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC"><code>date_trunc</code></a> method to truncate off the day (or whatever else you want, e.g., week, year, day, etc..)

Example of grouping sales from orders by month:

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

blocks|key|286694|text|您可以使用date_trunc(text,+timestamp)截断该月之后的所有信息|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|286695|select+date_trunc('month',created_at)::date+as+date+
from+orders+
order+by+date+DESC;|code-block|syntax|javascript|286696|​|286697|286698|示例：|BOLD|286699|286700|输入：|286701|286702|created_at+=+'2019-12-16+18:28:13'|286703|输出1:|286704|date_trunc('day',created_at)
//+2019-12-16+00:00:00|286705|输出2:|286706|date_trunc('day',created_at)::date+
//+2019-12-16|286707|输出3:|286708|date_trunc('month',created_at)::date+
//+2019-12-01|286709|输出4:|286710|date_trunc('year',created_at)::date+
//+2019-01-01|286711|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/9.1/static/functions-datetime.html^0|5|R|5|R|0|0|0|0|0|0|3|0|0|0|3|0|0|0|0|4|0|0|0|4|0|0|0|4|0|0|0|4|0|0^^$0|@$1|2|3|4|5|6|7|1K|8|@$9|1L|A|1M|B|C]]|D|@$9|1N|A|1O|1|1P]]|E|$]]|$1|F|3|G|5|H|7|1Q|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1R|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|1S|8|@]|D|@]|E|$]]|$1|N|3|O|5|6|7|1T|8|@$9|1U|A|1V|B|P]]|D|@]|E|$]]|$1|Q|3|L|5|6|7|1W|8|@]|D|@]|E|$]]|$1|R|3|S|5|6|7|1X|8|@$9|1Y|A|1Z|B|P]]|D|@]|E|$]]|$1|T|3|L|5|6|7|20|8|@]|D|@]|E|$]]|$1|U|3|V|5|H|7|21|8|@]|D|@]|E|$I|J]]|$1|W|3|X|5|6|7|22|8|@$9|23|A|24|B|P]]|D|@]|E|$]]|$1|Y|3|Z|5|H|7|25|8|@]|D|@]|E|$I|J]]|$1|10|3|11|5|6|7|26|8|@$9|27|A|28|B|P]]|D|@]|E|$]]|$1|12|3|13|5|H|7|29|8|@]|D|@]|E|$I|J]]|$1|14|3|15|5|6|7|2A|8|@$9|2B|A|2C|B|P]]|D|@]|E|$]]|$1|16|3|17|5|H|7|2D|8|@]|D|@]|E|$I|J]]|$1|18|3|19|5|6|7|2E|8|@$9|2F|A|2G|B|P]]|D|@]|E|$]]|$1|1A|3|1B|5|H|7|2H|8|@]|D|@]|E|$I|J]]|$1|1C|3|-4|5|6|7|2I|8|@]|D|@]|E|$]]]|1D|$1E|$5|1F|1G|1H|E|$1I|1J]]]]

You can truncate all information after the month using <a href="http://www.postgresql.org/docs/9.1/static/functions-datetime.html" rel="noreferrer"><code>date_trunc(text, timestamp)</code></a>:

<pre><code>select date_trunc('month',created_at)::date as date 
from orders 
order by date DESC;
</code></pre>

<hr/>
Example: 
Input: 

<pre><code>created_at = '2019-12-16 18:28:13'
</code></pre>

Output 1:

<pre><code>date_trunc('day',created_at)
// 2019-12-16 00:00:00
</code></pre>

Output 2:

<pre><code>date_trunc('day',created_at)::date 
// 2019-12-16
</code></pre>

Output 3:

<pre><code>date_trunc('month',created_at)::date 
// 2019-12-01
</code></pre>

Output 4:

<pre><code>date_trunc('year',created_at)::date 
// 2019-01-01
</code></pre>

blocks|key|3215827|text|第一个选项|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3215828|date_trunc('month',+timestamp_column)::date|code-block|syntax|javascript|3215829|它将保持从第一天开始的所有月份的日期格式。|3215830|示例：|3215831|2016-08-01
2016-09-01
2016-10-01
2016-11-01
2016-12-01
2017-01-01|3215832|第二个选项|3215833|to_char(timestamp_column,+'YYYY-MM')|3215834|@yairchu提出的这个解决方案在我的情况下工作得很好。我真的很想丢弃‘天’的信息。|3215835|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

1st Option

<pre><code>date_trunc('month', timestamp_column)::date
</code></pre>

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

Example:

<pre><code>2016-08-01
2016-09-01
2016-10-01
2016-11-01
2016-12-01
2017-01-01
</code></pre>

2nd Option

<pre><code>to_char(timestamp_column, 'YYYY-MM')
</code></pre>

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

blocks|key|3002614|text|您可以使用EXTRACT函数pgSQL|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3002615|EX-+date+=+1981-05-31
EXTRACT(MONTH+FROM+date)
it+will+Give+05|code-block|syntax|javascript|3002616|有关更多详细信息，请访问PGSQL+Date-Time|offset|length|3002617|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/static/functions-datetime.html^0|0|0|C|F|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

You Can use EXTRACT function pgSQL

<pre><code>EX- date = 1981-05-31
EXTRACT(MONTH FROM date)
it will Give 05
</code></pre>

For more details 
<a href="https://www.postgresql.org/docs/current/static/functions-datetime.html" rel="noreferrer">PGSQL Date-Time</a>

blocks|key|284583|text|它适用于“大于”函数，而不是小于。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|284584|例如：|284585|select+date_part('year',txndt)
from+"table_name"
where+date_part('year',txndt)+>+'2000'+limit+10;|code-block|syntax|javascript|284586|工作得很好。|284587|而是为了|284588|select+date_part('year',txndt)
from+"table_name"
where+date_part('year',txndt)+<+'2000'+limit+10;|284589|我得到了错误。|284590|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|X|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

It is working for "greater than" functions not for less than.

For example:

<pre><code>select date_part('year',txndt)
from "table_name"
where date_part('year',txndt) &gt; '2000' limit 10;
</code></pre>

is working fine.

but for 

<pre><code>select date_part('year',txndt)
from "table_name"
where date_part('year',txndt) &lt; '2000' limit 10;
</code></pre>

I am getting error.

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

<pre><code>to_char(timestamp_column, 'YYYY-MM')
</code></pre>

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

我想选择sql：SELECT "year-month" from table group by "year-month" AND order by date，其中year-month - format为日期"1978-01"，"1923-12“。选择couse work的to_char，但不是"right“order：...

问如何在不使用to_char()函数的情况下从PostgreSQL中的日期中提取年和月？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不使用to_char()函数的情况下从PostgreSQL中的日期中提取年和月？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不使用to_char()函数的情况下从PostgreSQL中的日期中提取年和月？
EN