I know the data types are TIMESTAMP, DATE, TIME, etc. I also know I should be using TIMESTAMP as it keeps track of any changes.
I am bringing the date from the front-end where in my react component the date is
const dateTime = new Date().toLocaleString("en-GB")
The output of which is "24/10/2021, 14:37:49"
If I set the data type to TIMESTAMP it gives me an error.
The question is what should be the data type to accept the above mentioned date and time format. I have tried DATETIME but it gives an error saying DATETIME does not exist. SQL query:
ALTER TABLE user_info_2 ADD date_col1 TIMEDATE;
I am new at PostgreSQL so any help is appreciated.
The issue is not the format per se, it is that the default Postgres Datestyle date ordering is:
--Note the MDY date ordering
show datestyle ;
DateStyle
-----------
ISO, MDY
select '24/10/2021, 14:37:49'::timestamptz;
ERROR: date/time field value out of range: "24/10/2021, 14:37:49"
LINE 1: select '24/10/2021, 14:37:49'::timestamptz;
---Change date ordering to put date first
set datestyle = iso,DMY;
show datestyle ;
DateStyle
-----------
ISO, DMY
(1 row)
select '24/10/2021, 14:37:49'::timestamptz;
timestamptz
------------------------
2021-10-24 14:37:49-07
So you have two choices:
Change the DateStyle 'permanently' in postgresql.conf or temporarily per session using set datestyle = iso,DMY;
Use the ISO format:
select '2021-10-24, 14:37:49'::timestamptz;
timestamptz
------------------------
2021-10-24 14:37:49-07
I would say 2) is the preferred choice as Postgres does not store the formatting so it really does matter how you input the data. If the date ordering is important for your users then do the appropriate formatting on presenting the output to them.
As I could see, using Date().toLocaleString() will return a string type. You should use a Date object to be persisted. For your column type in Postgres use TIMESTAMP.
typeof(Date().toLocaleString("en-GB"))
The output will be 'string'...