如何设置PostgreSQL默认值日期戳为'YYYYMM'?

Question

如标题所述，我该如何设置表格列的默认值为当前年月，格式为“YYYYMM”，例如今天是201912，则默认值应为201912？

- Strae

Answer 1

请记住，日期的格式化与存储是独立的。如果您需要在存储时使用该格式，则需要定义自定义数据类型或将其存储为字符串。然后，您可以使用提取(extract)、类型转换和连接(concatenation)的组合来获得该格式。

但是，我猜想您想要存储一个日期并在输出时获取格式。因此，像这样的方法就可以满足您的需求：

    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函数)

那么你就可以像这样获得输出:

SELECT id, to_char(my_date, 'yyyymm') FROM my_table;

如果你确实需要将该字段存储为字符串并确保格式，你可以这样做：

CREATE TABLE my_other_table
(
id serial PRIMARY KEY not null,
my_date varchar(6) default to_char(CURRENT_DATE, 'yyyymm')
);

- Nic Gibson

Answer 2

万一Milen A. Radev没能发布他的解决方案，这是它：

CREATE TABLE foo (
    key     int PRIMARY KEY,
    foo     text NOT NULL DEFAULT TO_CHAR(CURRENT_TIMESTAMP,'YYYYMM')
);

- cjs

---

这个类似的答案去哪了？ - Strae

Answer 3

为什么要这样做？

我认为您应该将日期存储为默认类型，如果需要，可以将其转换为所需格式进行获取。

您可以使用视图来指定列的格式，但是我不知道其他方法。

编辑：

说真的，在我看来，您应该在具有日期类型的表上创建一个视图。我说的是像这样的东西：

create table sample_table ( id serial primary key, timestamp date); 

and than

create view v_example_table as select id, to_char(date, 'yyyymmmm');

在您的应用程序中使用v_example_table。

- Marcin Cylke

---

因为我需要它。我有一个PHP应用程序，运行并查询数据库使用这些值，“yyyymm”作为“200801”，“200802”等来检索数据 - 我认为将其作为默认值比选择字段从表中更好，其中列介于时间戳1和时间戳2之间。 - Strae

我会使用我在回复中添加的解决方案来完成这个。 - Marcin Cylke

Answer 4

感谢所有回答的人，也感谢那些给了我函数格式化想法的人，我会在将来的使用中认真学习它。

但是对于这个明确的情况，“特殊yyyymm字段”不应被视为日期字段，而只是一个标签，或者用于匹配所需的年月值; 已经有另一个日期字段，具有完整的时间戳，但如果我需要2008年1月的所有行，则认为像以下选择更快：

SELECT  [columns] FROM table WHERE yearmonth = '200801'

替代

SELECT  [columns] FROM table WHERE date BETWEEN DATE('2008-01-01') AND DATE('2008-01-31')

- Strae

Answer 5

有一个常见的误解，认为可以通过这种方式进行反规范化以提高性能。对于您的查询，请使用date_trunc('month', date)，如果发现运行缓慢，则为此添加索引表达式。

- Charlie Clark

Answer 6

我字面上理解你的问题。 因为你不能。 务必至少有"Y"，"M"，"D" 这3个元素同时存在的日期戳。

表8.10显示了日期类型的一些可能输入。
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE。

Example     Description
1999-01-08  ISO 8601; January 8 in any mode (recommended format)
January 8, 1999     unambiguous in any datestyle input mode
1/8/1999    January 8 in MDY mode; August 1 in DMY mode
1/18/1999   January 18 in MDY mode; rejected in other modes
01/02/03    January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode
1999-Jan-08     January 8 in any mode
Jan-08-1999     January 8 in any mode
08-Jan-1999     January 8 in any mode
99-Jan-08   January 8 in YMD mode, else error
08-Jan-99   January 8, except error in YMD mode
Jan-08-99   January 8, except error in YMD mode
19990108    ISO 8601; January 8, 1999 in any mode
990108  ISO 8601; January 8, 1999 in any mode
1999.008    year and day of year
J2451187    Julian date
January 8, 99 BC    year 99 BC

日期输出：

datestyle Setting   Input Ordering  Example Output
SQL, DMY    day/month/year  17/12/1997 15:37:16.00 CET
SQL, MDY    month/day/year  12/17/1997 07:37:16.00 PST
Postgres, DMY   day/month/year  Wed 17 Dec 07:37:16 1997 PST

- jian

Answer 7

没错。最好使用一个函数：

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;

这给了我

 key | colname 
-----+---------
   0 | 200905

如果需要，请确保您从Unix命令行运行createlang plpgsql。

- cjs

---

或者在psql中执行"create language plpgsql"命令，这与createlang命令的作用相同。 - araqnid