如何在 POSTGRESQL 中从日期中提取年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36203613/
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 from DATE in POSTGRESQL
提问by Karthi
Date is in 'YYYY-MM-DD' text format, now I need to extract the year part which must be in numeric. I need this conversion to be done in single step, Since I need to use in other application where i cannot create new variable.
日期是 'YYYY-MM-DD' 文本格式，现在我需要提取必须是数字的年份部分。我需要单步完成此转换，因为我需要在无法创建新变量的其他应用程序中使用。
TO_DATE(t0.AESTDTC,'YYYY-MM-DD'),'YYYY-MM-DD' with this i was able to convert to date but Now i need to Extract the year from this date in single step? can any one help me?
TO_DATE(t0.AESTDTC,'YYYY-MM-DD'),'YYYY-MM-DD' 有了这个我可以转换为日期，但现在我需要一步一步地从这个日期中提取年份？谁能帮我？
回答by flaviodesousa
Try
尝试
select date_part('year', your_column) from your_table;
or
或者
select extract(year from your_column) from your_table;
回答by Hasan BINBOGA
answer is;
答案是；
select date_part('year', timestamp '2001-02-16 20:38:40') as year,
date_part('month', timestamp '2001-02-16 20:38:40') as month,
date_part('day', timestamp '2001-02-16 20:38:40') as day,
date_part('hour', timestamp '2001-02-16 20:38:40') as hour,
date_part('minute', timestamp '2001-02-16 20:38:40') as minute
回答by Zon
Choose one from, where :my_dateis a string input parameter of yyyy-MM-ddformat:
从中选择一个，其中:my_date是yyyy-MM-dd格式的字符串输入参数：
SELECT EXTRACT(YEAR FROM CAST(:my_date AS DATE));
or
或者
SELECT DATE_PART('year', CAST(:my_date AS DATE));
Better use CASTthan ::as there may be conflicts with input parameters.
更好地使用CAST，::因为可能与输入参数发生冲突。
回答by rktuxyn
You may try to_char(now()::date, 'yyyy')
If text, you've to cast your text to date to_char('2018-01-01'::date, 'yyyy')
您可以尝试to_char(now()::date, 'yyyy')
If text, you've to cast your text to dateto_char('2018-01-01'::date, 'yyyy')
See the PostgreSQLDocumentation Data Type Formatting Functions
请参阅PostgreSQL文档数据类型格式化函数
回答by ghimirehillson
This line solved my same problem in postgresql:
这一行解决了我在 postgresql 中的同样问题：
SELECT DATE_PART('year', column_name::date) from tableName;
If you want month, then simply replacing yearwith monthsolves that as well and likewise.
如果你想要month，那么简单地用替换也year可以month解决这个问题。
回答by SR_Mehta
you can also use just like this in newer version of sql,
您也可以在较新版本的 sql 中像这样使用，
select year('2001-02-16 20:38:40') as year,
month('2001-02-16 20:38:40') as month,
day('2001-02-16 20:38:40') as day,
hour('2001-02-16 20:38:40') as hour,
minute('2001-02-16 20:38:40') as minute
