How to substract a second (or a minute) from a date field?!?

[hansdevr]

I need to fill a new field in a table with the date from a field in a previous record.

The latter contains a date in the following format: yyyy-mm-dd hh:mm:ss:xxxxxxx (not sure what those xx's are). Example:

2014-11-14 02:00:50:3930000

I need to use that value in a (new) field in the current record, but I need to substract a second or a minute from that value.

I have been searching, but couldn't find an answer...

[sunny_talwar · Accepted solution]

Try this to subtract a second:

=Timestamp(Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'ss'), 'YYYY-MM-DD hh:mm:ss:fffffff')

To subtract a minute, try this:

=Timestamp(Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'mm'), 'YYYY-MM-DD hh:mm:ss:fffffff')

[hansdevr]

For a subtraction, your solution worked best, Sunny!

Now trying to implement this in a LOAD script....

[sunny_talwar]

Try this in the script:

vTimeStamp = Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff')

vSubSeconds = Interval#('1', 'ss')

vSubMinutes = Interval#('1', 'mm')

vOutput1 = TimeStamp($(vTimeStamp) - $(vSubSeconds), 'YYYY-MM-DD hh:mm:ss:fffffff')

vOutput2 = TimeStamp($(vTimeStamp) - $(vSubMinutes), 'YYYY-MM-DD hh:mm:ss:fffffff')

LET vTimeStamp = Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff');

LET vSubSeconds = Interval#('1', 'ss');

LET vSubMinutes = Interval#('1', 'mm');

LET vOutput1 = TimeStamp($(vTimeStamp) - $(vSubSeconds), 'YYYY-MM-DD hh:mm:ss:fffffff');

LET vOutput2 = TimeStamp($(vTimeStamp) - $(vSubMinutes), 'YYYY-MM-DD hh:mm:ss:fffffff');

[hansdevr]

Thank you very much!! I am impressed with the speed this community responds to questions!!

[sunny_talwar]

Actually this will work:

LET vTimeStamp = Num(Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff'));

LET vSubSeconds = Num(Interval#('1', 'ss'));

LET vSubMinutes = Num(Interval#('1', 'mm'));

LET vOutput1 = TimeStamp($(vTimeStamp) - $(vSubSeconds), 'YYYY-MM-DD hh:mm:ss:fffffff');

LET vOutput2 = TimeStamp($(vTimeStamp) - $(vSubMinutes), 'YYYY-MM-DD hh:mm:ss:fffffff');

[hansdevr]

Your solution works, but not during a load script, in which I can't use your variable vTimeStamp..

Let's suppose I have this VERY simplified script:

LOAD
"creation_date";
SQL SELECT *
FROM "blahblahblah";

And I would like to have it look something like this:

LOAD
"creation_date",
(subtract one minute from creation_date)    as "end_date_min",
(subtract one second from creation_date)   as "end_date_sec";

SQL SELECT *
FROM "blahblahblah";

(which is of course total nonsense script), how would I go about it?

[sunny_talwar]

Try this may be:

LOAD
"creation_date";
SQL SELECT *
FROM "blahblahblah";

And I would like to have it look something like this:

LOAD
"creation_date",
Timestamp(Timestamp#(creation_date, 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'ss'), 'YYYY-MM-DD hh:mm:ss:fffffff')  as "end_date_sec",
Timestamp(Timestamp#(creation_date, 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'mm'), 'YYYY-MM-DD hh:mm:ss:fffffff')  as "end_date_min";

SQL SELECT *
FROM "blahblahblah";

Important thing to note here is that we are creating two new columns which are one second (end_date_sec) and one minute (end_date_min) behind creation_date for each row. Is this what you are looking to do?

[hansdevr]

Yes, that is EXACTLY what I'm looking for!

But alas, I got this when I looked at table example:

[sunny_talwar]

Your TimeStamp field is this format: hh:mm:ss.fffffff?? no date attached to it? If this is how it is, then try this:

LOAD
"creation_date";
SQL SELECT *
FROM "blahblahblah";

And I would like to have it look something like this:

LOAD
"creation_date",
Time(Time#(creation_date, 'hh:mm:ss:fff') - Interval#('1', 'ss'), 'hh:mm:ss:fffffff')  as "end_date_sec",
Time(Time#(creation_date, 'hh:mm:ss:fff') - Interval#('1', 'mm'), 'hh:mm:ss:fffffff')  as "end_date_min";

SQL SELECT *
FROM "blahblahblah";

[hansdevr]

Still the same result...