Re: change year in timestamp

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> How can i change the year in a timestamp ?
> i.e. from 2019-06-11 19:38:29+03 to 2003-06-11 19:38:29+03

Not sure what you are asking, but perhaps something like this:

CREATE TABLE mytimes (a TIMESTAMPTZ);

INSERT INTO mytimes(a) VALUES ('2019-06-11 19:38:29');

SELECT * FROM mytimes;

           a
- ------------------------
 2019-06-11 19:38:29-04


To subtract 16 years from all rows in the table:

UPDATE mytimes SET a = a - '16 years'::interval;

SELECT * FROM mytimes;

           a
- ------------------------
 2003-06-11 19:38:29-04


To force all years to 2003 regardless of their initial state is a little trickier:

INSERT INTO mytimes(a) VALUES ('2019-06-17 21:38:22');
INSERT INTO mytimes(a) VALUES ('2020-02-14 05:31:03');
INSERT INTO mytimes(a) VALUES ('1968-01-22 07:02:05');

SELECT * FROM mytimes;

           a
- ------------------------
 2003-06-11 19:38:29-04
 2019-06-17 21:38:22-04
 2020-02-14 05:31:03-05
 1968-01-22 07:02:05-05


UPDATE mytimes SET a = a + (2003-EXTRACT(YEAR FROM a) || ' years')::interval;

SELECT * FROM mytimes;

           a
- ------------------------
 2003-06-11 19:38:29-04
 2003-06-17 21:38:22-04
 2003-02-14 05:31:03-05
 2003-01-22 07:02:05-05

What we did was use EXTRACT to grab the year from each timestamp, figure out
how many years off it was from 2003, and use that to adjust the timestamp
accordingly.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302141109

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+TRTGvJuQZxSWSsgRAttQAJ4xd8km0Lc2+I7ij4aXgM29mfx8uQCg0n3G
T0RBAWp0DSZBNPUieUWnNkA=
=2/g8
-----END PGP SIGNATURE-----