Change Sysdate for tests purposes

Moderator: NorbertKrupa

Post Reply
Canarchiste
Newbie
Newbie
Posts: 7
Joined: Mon Aug 22, 2016 6:24 am

Change Sysdate for tests purposes

Post by Canarchiste » Wed Nov 16, 2016 11:16 am

Hi everyone,

I am developing some data interface loading and computing data from the previous day (today-1) running everyday.

For obvious reason I am currently running it into production conditions, and I have to deal with some set of old data.

So any sysdate()-1 need to be adjusted everyday, which is pretty heavy to do (especially when you have to deal with data from 2011).

There is a way to modify sysdate into Oracle for tests purpose (we put a fix value into sysdate, then after the test we get sysdate() back to the proper value) :
http://nuijten.blogspot.sg/2010/11/chan ... sting.html

I haven't seen any option into the documentation but I might have missed something.
Is it possible ?

Another way would be to set an internal variable and replace every sysdate() by it :

Code: Select all

\set test '''11-11-2015 11:11:11'''::timestamp
select to_timestamp(:test, 'DD/MM/YYYY HH24:MI:SS') -1 as test;
I can also pass a date into parameter when the vsql scripts are called by bash script.

But it implies pretty heavy changes into my scripts just for testing so I would like to avoid this.

Thank you.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Change Sysdate for tests purposes

Post by JimKnicely » Thu Nov 17, 2016 9:31 pm

Hmm.

Maybe you can play around with the session timezone?

Code: Select all

dbadmin=> show timezone;
   name   |  setting
----------+------------
 timezone | US/Eastern
(1 row)

dbadmin=> select sysdate;
          sysdate
----------------------------
 2016-11-17 15:25:00.860848
(1 row)

dbadmin=> SET TIME ZONE TO INTERVAL '-14:00 HOURS';
SET
dbadmin=> select sysdate;
          sysdate
----------------------------
 2016-11-17 06:25:10.275738
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Canarchiste
Newbie
Newbie
Posts: 7
Joined: Mon Aug 22, 2016 6:24 am

Re: Change Sysdate for tests purposes

Post by Canarchiste » Mon Nov 21, 2016 4:53 am

Thank you very much for your answer Jim.

Poorly, as far as I need to set the sysdate to 2015 (or further), this play-around doesn't suit (maximum of 14 hours back in time).

Or maybe I misunderstood something ?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Change Sysdate for tests purposes

Post by JimKnicely » Mon Nov 28, 2016 2:22 pm

Hi,

Maybe you can add a column to your table that has a default of the day before your date column? Then change your queries to use that column:

Code: Select all

dbadmin=> create table test (date_today timestamp);
CREATE TABLE

dbadmin=> insert into test select sysdate;
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test;
         date_today
----------------------------
 2016-11-28 08:16:48.964127
(1 row)

dbadmin=> alter table test add column date_yesterday timestamp default date_today - 1;
ALTER TABLE

dbadmin=> select * from test;
         date_today         |       date_yesterday
----------------------------+----------------------------
 2016-11-28 08:16:48.964127 | 2016-11-27 08:16:48.964127
(1 row)
Or so that you don't have to change your code, maybe you can rename the table to something else and then create a view that queries the renamed table and the view could have a calculated date for the column in question.

Code: Select all

dbadmin=> drop table test;
DROP TABLE

dbadmin=> create table test (date_today timestamp);
CREATE TABLE

dbadmin=> insert into test select sysdate;
 OUTPUT
--------
      1
(1 row)

dbadmin=> select date_today from test;
         date_today
----------------------------
 2016-11-28 08:20:45.440376
(1 row)

dbadmin=> alter table test rename to test1;
ALTER TABLE

dbadmin=> create view test as select date_today - 1 as date_today from test1;
CREATE VIEW

dbadmin=> select date_today from test;
         date_today
----------------------------
 2016-11-27 08:20:45.440376
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Canarchiste
Newbie
Newbie
Posts: 7
Joined: Mon Aug 22, 2016 6:24 am

Re: Change Sysdate for tests purposes

Post by Canarchiste » Fri Dec 02, 2016 8:22 am

Thanks for your answer.

Poorly there is 40+ tables involved, I can't add columns only for test purpose.

I will use the variable solution.

Thanks again for your help !

Post Reply

Return to “Vertica SQL Functions”