0

In a PostgreSQL table, I have a column named insert_date of type varchar(114) with values stored as below:

2023-04-08 01:42:12.197779000 +01:00

Now I would like to add or subtract a number of years to the timestamp, according to the business logic. For example, if I decrease the year value by 3. it should become like this:

2020-04-08 01:42:12.197779000 +01:00

I tried to update but was not able to change the year and value in the same format.

1 Answer 1

0

You could first use to_timestamp to convert the string to a timestamp, then perform date arithmetic:

SELECT to_timestamp(
          '2023-04-08 01:42:12.197779000 +01:00',
          'YYYY-MM-DD HH24:MI:SS.FF6000 TZH:TZM'
       ) - 3 * INTERVAL '1 year';

           ?column?            
═══════════════════════════════
 2020-04-08 02:42:12.197779+02
(1 row)

Note that PostgreSQL only supports microsecond precision for timestamps.

To convert the result back to a string, use to_date():

SELECT to_char(
          to_timestamp(
             '2023-04-08 01:42:12.197779000 +01:00',
             'YYYY-MM-DD HH24:MI:SS.FF6000 TZH:TZM'
          ) - 3 * INTERVAL '1 year',
          'YYYY-MM-DD HH24:MI:SS.FF6000 TZH:TZM'
       );

               to_char                
══════════════════════════════════════
 2020-04-08 02:42:12.197779000 +02:00
(1 row)
5
  • Thanks for your Answer.But when I am executing the same query provided by your it is giving below error SQL Error [22007]: ERROR: Invalid date format: Specified time zone twice
    – Hnarang
    Jul 10, 2023 at 11:58
  • Can you please tell me, why it is giving this error.Because if you have executed it successfully. it should not give me any error in this
    – Hnarang
    Jul 10, 2023 at 11:59
  • Either you made a spelling error, or the data look different from what you showed. Jul 10, 2023 at 12:49
  • I just copied your code and executed it in postgresql DB through DBeaver tool. still it is giving the same error. If possible please guide me
    – Hnarang
    Jul 11, 2023 at 12:54
  • Well, if I cannot reproduce what you see, I cannot help you. I see no way that my two SQL statements can produce an error. Besides, that error message does not exist in the PostgreSQL code base. You must be using something else, like a fork of PostgreSQL. Jul 11, 2023 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.