0

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.

Brute
  • 121
  • 1
  • 10
  • 1
    "*what should be the data type to accept the above mentioned date and time format*" - you're asking the wrong question. Don't use that British format for anything but displaying timestamps to British users! Use [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) for sending timestamps (e.g. from frontend to backend, from backend to database, or reverse) like everyone else. – Bergi Oct 24 '21 at 22:52
  • @Bergi I appriciate you taking out time to reply. But what if this is for British users then what? – Brute Oct 24 '21 at 22:58
  • Then still use ISO 8601. – Bergi Oct 24 '21 at 23:02
  • @Brute—Bergi is making a distinction between what you store in the database and what you display to users. – RobG Oct 25 '21 at 05:46

2 Answers2

1

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:

  1. Change the DateStyle 'permanently' in postgresql.conf or temporarily per session using set datestyle = iso,DMY;

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

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
0

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

Igor R. Braga
  • 91
  • 4