Insert data of timestamp type in PostgreSQL database

Question

Insert data of timestamp type in PostgreSQL database

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

How can I insert the date and time in a PostgreSQL database where the field is of type timestamp ?

I tried the following query in PostgreSQL , but it gives an error that the date is not compatible with timestamp :

Code:

insert into tabla (nombre, ctime, mtime) values ('pepe', '2017-05-2', '2017-05-2')

postgresql postgresql-9.3

asked by Dyn 05.05.2017 в 06:22

source

2 answers

Run several prepared queries Selenium to complete a form of a pop-up window in JAVA

score 1 · Answer 1

You must use postgreSQL date / time functions, for example:

to_timestamp(text, text)

insert into tabla (nombre, ctime, mtime) 
values ('pepe',
to_timestamp('02 May 2017', 'DD Mon YYYY'), 
to_timestamp('02 May 2017', 'DD Mon YYYY')
);

Here you have more information about the functions / date times: link

score 0 · Answer 2

You speak of "insert date and time" but in your example it is only a date. In any case, normally that format should work.

To make sure, it is enough that you try to make a cast, without using any table:

Ex:

select  '2017-05-2'::timestamp;
      timestamp
---------------------
 2017-05-02 00:00:00

Actually, the date format that Postgresql issues (and accepts) depends on a configuration parameter DATESTYLE

By default, the ISO format is accepted (and recommended). The format of dates with ISO is YEAR-MONTH-DAY.

To know what your datestyle is:

show datestyle;
 DateStyle
-----------
 ISO, MDY