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@xxxxxxxxxxxx
PGP Key: 0x14964AC8 200302141109

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

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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)