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