how to create date

Question

how to create date format yyyy-mm with postgresql11

CREATE TABLE public."ASSOL"
(
    id integer NOT NULL,
    "ind" character(50) ,
    "s_R" character(50) ,
    "R" character(50) ,
    "th" character(50),
    "C_O" character(50) ,
    "ASSOL" numeric(11,3),
    date date,
    CONSTRAINT "ASSOL_pkey" PRIMARY KEY (id)

Possible duplicate of [PostgreSQL - Create table and set specific date format](https://stackoverflow.com/questions/21019674/postgresql-create-table-and-set-specific-date-format) — krithikaGopalakrishnan, May 23 '19 at 09:35
Why exactly do you think you need this specific format stored inside the table? The "format" as you say is just a specific representation and has nothing to do with an actually stored value. It's just like you'd expect your computer to hold the number `123` exactly in that way in memory, although your computer stores it as `00000000000000000000000001111011` if interpreted as 32-bit integer. I hope you get the point. — Ancoron, May 23 '19 at 21:11

score 1 · Answer 1 · answered May 23 '19 at 11:01

This is a variation of Kaushik's answer.

You should just use the date data type. There is no need to create another type for this. However, I would implement this use a check constraint:

CREATE TABLE public.ASSOL (
    id serial primary key,
    ind varchar(50) ,
    s_R varchar(50) ,
    R varchar(50) ,
    th varchar(50),
    C_O varchar(50) ,
    ASSOL numeric(11,3),
    yyyymm date,
    constraint chk_assol_date check (date = date_trunc('month', date))
);

This only allows you to insert values that are the first day of the month. Other inserts will fail.

Additional notes:

Don't use double quotes when creating tables. You then have to refer to the columns/tables using double quotes, which just clutters queries. Your identifiers should be case-insensitive.
An integer primary key would normally be a serial column.
NOT NULL is redundant for a PRIMARY KEY column.
Use reasonable names for columns. If you want a column to represent a month, then yyyymm is more informative than date.
Postgres stores varchar() and char() in the same way, but for most databases, varchar() is preferred because trailing spaces actually occupy bytes on the data pages.

score 0 · Answer 2 · answered May 23 '19 at 09:32

0

for year and month you can try like below

SELECT to_char(now(),'YYYY-MM') as year_month

   year_month
   2019-05

answered May 23 '19 at 09:32

Zaynul Abadin Tuhin

31,407
5
33
63

i want create column in table format date yyyy-mm but not get time from system – Dell May 23 '19 at 09:38
when i create column – Dell May 23 '19 at 09:38
date date ::::> result yyyy-mm-dd – Dell May 23 '19 at 09:39

score 0 · Answer 3 · answered May 23 '19 at 10:50

You cannot create a date datatype that stores only the year and month component. There's no such option available at the data type level.

If you want to to truncate the day component to default it to start of month, you may do it. This is as good as having only the month and year component as all the dates will have day = 1 and only the month and year would change as per the time of running insert.

For Eg:

create table t ( id int, col1 text,   
                 "date" date default date_trunc('month',current_date) );

insert into t(id,col1) values ( 1, 'TEXT1');

select * from t

d   col1    date
1   TEXT1   2019-05-01

If you do not want to store a default date, simply use the date_trunc('month,date) expression wherever needed, it could either be in group by or in a select query.

how to create date

3 Answers3