如何设置Postgresql默认值日期戳如“YYYYMM”？ [英] How to set a Postgresql default value datestamp like 'YYYYMM'?

问题描述

作为标题，如何设置表格的列为当前年份和月份的默认值，格式为YYYYMM，如今天的200905？

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?

推荐答案

请记住，日期的格式与存储无关。如果您对日期是必需的，则以该格式存储 ，则需要定义自定义数据类型或将其存储为字符串。然后，您可以使用提取，类型转换和级联获得该格式。

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 is stored in 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.

但是，我怀疑要存储日期并获取输出格式。所以，这样的事情会为你做点事：

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).

（修改为使用to_char）。

(Edited to use to_char).

你可以得到你的输出：

select id, to_char(my_date, 'yyyymm') from my_table;

现在，如果 确实需要将该字段存储为字符串并确保您可以随时执行的格式：

Now, if you did really 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')
);

这篇关于如何设置Postgresql默认值日期戳如“YYYYMM”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！