Ошибка значение поля типа date time вне диапазона

Seconds since epoch timestamps

Your «timestamp» is in seconds-since-epoch. Per dezso, use to_timestamp(). I missed that when I checked dfS+

Bad complex idea, and dragons.

Check the input formats. They cover casts from strings. That’s what COPY is doing under the hood. The only method that even remotely looks like a long number is ISO 8601. If you look at that example though you’ll see it’s not a seconds-since-epoch

Example    | Description
19990108   | ISO 8601; January 8, 1999 in any mode

This is basically the same as another example on that chart.

Example    | Description
1999-01-08 | ISO 8601; January 8, 1999 in any mode

Converting to timestamp with abstime as an intermediary format

So if you want to convert from seconds-since-epoch, you can cheat by using the internal abstime since there is no available cast directly to timestamp from a string of seconds-since-epoch.

SELECT 1421088300::abstime::timestamp;
      timestamp      
---------------------
 2015-01-12 12:45:00
(1 row)

What’s happening here is that abstime is binary coercable with integer. You can see that in dC+. I checked dfS+ for functions to get from integer to timestamp and found none. There is a cast though from integer to abstime (which is stored as an integer), and from abstime to timestamp.

If this is a new table you could actually type that column as abstime. It should load perfectly fine. And then you can ALTER TABLE. here is an example, except I’m not running COPY (but it should work all the same).

CREATE TABLE foo(bar)
AS VALUES
  (1421088300::abstime);

TABLE foo;
          bar           
------------------------
 2015-01-12 12:45:00-06
(1 row)

ALTER TABLE foo
  ALTER bar
  TYPE timestamp;

TABLE foo;
         bar         
---------------------
 2015-01-12 12:45:00
(1 row)

d foo;
                Table "public.foo"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 bar    | timestamp without time zone | 

Software:

Linux cjz-eshop1-p 5.4.0-33-generic #37-Ubuntu SMP Thu May 21 12:53:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)

I am getting errors due to CSV import into the database on my server.

ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244385 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160
ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244386 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160

But on my local system, the import is working.

I tried almost everything regarding this issue, but no luck (I drew mostly from here).

Any ideas to solve this issue?

UPDATE

Import is done by java. I believe it is not important, because on my local computer, it is working with same OS and same configuration. And as you can see, when I try select related to date (as @Laurenz helped me), it is ok.

postgres@cjz-eshop1-p:~$ psql
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

postgres=# SELECT '30.06.2020'::date;
    date    
------------
 30.06.2020
(1 row)

postgres=# 

Before, it was not possible. Do you want some more details?

UPDATE 2

                    if (body[5].length() != 0) {
                        akc_from = "'" + body[5] + "'";
                    } else {
                        akc_from = "null";
                    }

                    if (body[6].length() != 0) {
                        akc_to = "'" + body[6] + "'";
                    } else {
                        akc_to = "null";
                    }

                    if (body[7].length() != 0) {
                        akc_type = body[7];
                    } else {
                        akc_type = "null";
                    }

                    insertPriceCommand = "insert into stg_price(art_no,store_no,sell_pr,akc_price,akc_from,akc_to,akc_type,run_id,proc_flag,proc_note) values(" + art_no + "," + store_no + "," + sell_pr + "," + akc_price + "," + akc_from + "," + akc_to + "," + akc_type + "," + run_id + ",0,null)";
//                System.out.println(insertPriceCommand);
```

Software:

Linux cjz-eshop1-p 5.4.0-33-generic #37-Ubuntu SMP Thu May 21 12:53:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)

I am getting errors due to CSV import into the database on my server.

ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244385 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160
ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244386 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160

But on my local system, the import is working.

I tried almost everything regarding this issue, but no luck (I drew mostly from here).

Any ideas to solve this issue?

UPDATE

Import is done by java. I believe it is not important, because on my local computer, it is working with same OS and same configuration. And as you can see, when I try select related to date (as @Laurenz helped me), it is ok.

postgres@cjz-eshop1-p:~$ psql
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

postgres=# SELECT '30.06.2020'::date;
    date    
------------
 30.06.2020
(1 row)

postgres=# 

Before, it was not possible. Do you want some more details?

UPDATE 2

                    if (body[5].length() != 0) {
                        akc_from = "'" + body[5] + "'";
                    } else {
                        akc_from = "null";
                    }

                    if (body[6].length() != 0) {
                        akc_to = "'" + body[6] + "'";
                    } else {
                        akc_to = "null";
                    }

                    if (body[7].length() != 0) {
                        akc_type = body[7];
                    } else {
                        akc_type = "null";
                    }

                    insertPriceCommand = "insert into stg_price(art_no,store_no,sell_pr,akc_price,akc_from,akc_to,akc_type,run_id,proc_flag,proc_note) values(" + art_no + "," + store_no + "," + sell_pr + "," + akc_price + "," + akc_from + "," + akc_to + "," + akc_type + "," + run_id + ",0,null)";
//                System.out.println(insertPriceCommand);
```

Привет, я пытаюсь вставить дату в свою таблицу. Но я получаю ошибку. Я также
попробовал два решения, которые я нашел:

set datestyle to SQL,DMY;

set datestyle = dmy;

Но проблема все еще существует. Пожалуйста, помогите, где я делаю ошибку. Это ошибка:

Error Number:

ERROR: date/time field value out of range: "24-07-2016" LINE 1: ...isalabad', E'Chief Executive ', E'1994-10-13', E'24-07-20... ^ HINT: Perhaps you need a different "datestyle" setting.

INSERT INTO "employee" ("fullname", "loginname", "email", "password", "phone", "cnic", "address", "jobdescription", "birthdate", "registered", "qualification", "lastcompany", "lastsalary", "status", "location", "department", "designation", "path") VALUES ( E'Shahzeb Akram', E'shaizi96', E'[email protected]', E'1234', E'3137688894', E'33100-8398084-5', E'Faisalabad, Faisalabad', E'Chief Executive ', E'1994-10-13', E'24-07-2016', E'BSCS', E'BOLT', E'121212', E'1', '', E'39', E'43', E'http://localhost/department/uploads/Shahzeb Akram/Screenshot_(1).png')

Filename: C:/wamp/www/department/system/database/DB_driver.php

Line Number: 691