Thread: change year in timestamp

Hi,

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


Thanks,

-- argo

-----------------------------------------
Hot Mobiil - helinad, logod ja piltsõnumid!
http://portal.hot.ee

-----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-----

argo......

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

Looks similiar to a problem I had, moving classified expirations nearer in
time to get them out of the database. (I send emails to the users telling
them of the pending expiration of their listing).

While I could have certainly have written a perl script to parse out the
timestamp components and reassemble them again what I did was update all
the rows posted a certain month from years past that expire way in the
future and move them all to expire on a date in the near future.  I moved
them month by month to expire on different days in the near future.

Of course I could do that because I have both date_posted and date_expire
timestamps for each record.

Maybe some of the SQL gurus here can tell us an even easier way.

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
     Check out my Musician's Online Database Exchange (The MODE Pages)
                        http://www.TheMode.com
 ==========================================================================