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]

Missed a small detail (in red)

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]

Nope, same result.

Here's a list of the SET variables in my main Tab:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='D-M-YYYY';
SET TimestampFormat='D-M-YYYY hh:mm:ss[.fff]';
SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;di;wo;do;vr;za;zo';

As you can see this is the formatting, commonly used in Europe, more specific The Netherlands. Could this affect your script?

[hansdevr]

When I do a selection on tables, however - and I select the tab example in the table view, my creation date looks like this:

[sunny_talwar]

‌Not sure what format is TimeStamp is, but if it does include date then do this:

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

[hansdevr]

Yeeeehaaaa!! That did the trick!! Thank you ever so much!!!

[jonathandienst]

For future reference, 1 minute = 1/1440 (24*60)and 1 second = 1/86400 (24*60*60)

You could use 1/86400 instead of Interval#('1', 'ss')

And

LET vSubSeconds = 1/86400;

LET vSubMinutes = 1/1440;

[Anonymous]

I know this has been answered but you can also do this to calculate seconds.

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

Dates and times are expressed in days and fractions of days so to subtract a day you can just say -1 an hour would be -1/24, a minute will be 1/24/60 and a second will be 1/24/60/60. This just saves a little time with syntax etc.

[hansdevr]

I tried your solution - which is indeed a lot less scripting - and it did also work!!

date((creation_date - $(vSubSeconds)), 'YYYY-MM-DD hh:mm:ss') as End_date;