I want to create a new table and set a date type with a specific format. Is that possible?
For example:
CREATE TABLE User (
...
EXPIRATION DATE " YYYY/MM"
...
)
I suggest a different approach: Never store date / time as character type (text
, varchar()
, ...) to begin with. Use an appropriate type, probably date
in your case.
Also, never use reserved words as identifier. user
is just not possible to begin with, you would have to double-quote, which I would discourage.
Could look like this:
CREATE TABLE usr (
usr_id serial PRIMARY KEY
,usr text UNIQUE
,expiration_date date
...
);
Now, various input formats are possible, as long as they are unambiguous. The related question @DrColossos has linked to in his comment has more on that.
The manual has all the details.
To enforce a particular input format, you could run the text literal through the to_date()
function. Example:
INSERT INTO usr (usr, expiration_date)
VALUES ('flippin_user', to_date($my_date_literal, ' YYYY/MM');
Note: if you include the leading blank in the pattern, it is expected from the input!
Finally, you can format your date any way you like with to_char()
on output:
SELECT usr, to_char(expiration_date, ' YYYY/MM') AS formatted_exp_date
WHERE usr_id = 1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With