postgresql 如何设置像'YYYYMM'这样的Postgresql默认值日期戳？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/910763/
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 set a Postgresql default value datestamp like 'YYYYMM'?
提问by Strae
As title, how can I set a table's column to have the default value the current year and month, in format 'YYYYMM', like 200905 for today?
作为标题，如何将表的列设置为当前年份和月份的默认值，格式为“YYYYMM”，例如今天的 200905？
回答by Nic Gibson
Please bear in mind that the formatting of the date is independent of the storage. If it's essential to you that the date is storedin that format you will need to either define a custom data type or store it as a string. Then you can use a combination of extract, typecasting and concatenation to get that format.
请记住，日期的格式与存储无关。如果日期以该格式存储对您来说很重要，您将需要定义自定义数据类型或将其存储为字符串。然后，您可以结合使用extract、 typecasting 和 concatenation 来获得该格式。
However, I suspect that you want to store a date and get the format on output. So, something like this will do the trick for you:
但是，我怀疑您想存储日期并获取输出格式。所以，像这样的事情会为你做的伎俩：
CREATE TABLE my_table
(
id serial PRIMARY KEY not null,
my_date date not null default CURRENT_DATE
);
(CURRENT_DATE is basically a synonym for now() and a cast to date).
(Edited to use to_char).
（编辑为使用 to_char）。
Then you can get your output like:
然后你可以得到你的输出：
SELECT id, to_char(my_date, 'yyyymm') FROM my_table;
Now, if you didreally need to store that field as a string and ensure the format you could always do:
现在，如果你也真的需要那场存储为一个字符串，并确保你总是可以做的格式为：
CREATE TABLE my_other_table
(
id serial PRIMARY KEY not null,
my_date varchar(6) default to_char(CURRENT_DATE, 'yyyymm')
);
回答by cjs
Just in case Milen A. Radev doesn't get around to posting his solution, this is it:
以防万一 Milen A. Radev 没有时间发布他的解决方案，就是这样：
CREATE TABLE foo (
key int PRIMARY KEY,
foo text NOT NULL DEFAULT TO_CHAR(CURRENT_TIMESTAMP,'YYYYMM')
);
回答by Marcin Cylke
Why would you want to do this?
你为什么想做这个？
IMHO you should store the date as default type and if needed fetch it transforming to desired format.
恕我直言，您应该将日期存储为默认类型，并在需要时将其转换为所需的格式。
You could get away with specifying column's format but with a view. I don't know other methods.
您可以避免指定列的格式，但可以使用视图。我不知道其他方法。
Edited:
编辑：
Seriously, in my opinion, you should create a view on that a table with date type. I'm talking about something like this:
说真的，在我看来，您应该在具有日期类型的表上创建一个视图。我正在谈论这样的事情：
create table sample_table ( id serial primary key, timestamp date);
and than
然后
create view v_example_table as select id, to_char(date, 'yyyymmmm');
And use v_example_table in your application.
并在您的应用程序中使用 v_example_table。
回答by Strae
Thanks for everyone who answered, and thanks for those who gave me the function-format idea, i'll really study it for future using.
感谢所有回答的人，感谢那些给我函数格式想法的人，我会认真研究它以备将来使用。
But for this explicit case, the 'special yyyymm field' is not to be considered as a date field, but just as a tag, o whatever would be used for matching the exactly year-month researched value; there is already another date field, with the full timestamp, but if i need all the rows of january 2008, i think that is faster a select like
但是对于这种明确的情况，“特殊 yyyymm 字段”不被视为日期字段，而只是一个标签，无论用于匹配精确的年月研究值的任何内容；已经有另一个日期字段，带有完整的时间戳，但是如果我需要 2008 年 1 月的所有行，我认为这样的选择会更快
SELECT [columns] FROM table WHERE yearmonth = '200801'
instead of
代替
SELECT [columns] FROM table WHERE date BETWEEN DATE('2008-01-01') AND DATE('2008-01-31')
回答by Charlie Clark
It's a common misconception that you can denormalise like this for performance. Use date_trunc('month', date)for your queries and add an index expression for this if you find it running slow.
一个常见的误解是，您可以像这样对性能进行非规范化。date_trunc('month', date)如果您发现它运行缓慢，请用于您的查询并为此添加索引表达式。
回答by cjs
Right. Better to use a function:
对。最好使用一个函数：
CREATE OR REPLACE FUNCTION yyyymm() RETURNS text
LANGUAGE 'plpgsql' AS $$
DECLARE
retval text;
m integer;
BEGIN
retval := EXTRACT(year from current_timestamp);
m := EXTRACT(month from current_timestamp);
IF m < 10 THEN retval := retval || '0'; END IF;
RETURN retval || m;
END $$;
SELECT yyyymm();
DROP TABLE foo;
CREATE TABLE foo (
key int PRIMARY KEY,
colname text DEFAULT yyyymm()
);
INSERT INTO foo (key) VALUES (0);
SELECT * FROM FOO;
This gives me
这给了我
key | colname
-----+---------
0 | 200905
Make sure you run createlang plpgsqlfrom the Unix command line, if necessary.
createlang plpgsql如有必要，请确保从 Unix 命令行运行。
