Here’s a bit of code that I just developed.
<@IF <@ISNUM <@COLUMN 'DATEDIFF(mi,date_created,date_ended)'>>>
<@IF "<@COLUMN 'DATEDIFF(mi,date_created,date_ended)'> < 60">
<@COLUMN 'DATEDIFF(mi,date_created,date_ended)'>m
<@ELSEIF "<@COLUMN 'DATEDIFF(mi,date_created,date_ended)'> < 1440">
<@CALC "floor(<@COLUMN 'DATEDIFF(mi,date_created,date_ended)'> /
60)" precision=0>h
<@CALC "<@COLUMN 'DATEDIFF(mi,date_created,date_ended)'> % 60">m
<@ELSE>
<@ASSIGN days <@CALC "floor(<@COLUMN
'DATEDIFF(mi,date_created,date_ended)'> / 1440)" precision=0>><@VAR days>d
<@CALC "floor((<@COLUMN
'DATEDIFF(mi,date_created,date_ended)'>-(@@days * 1440)) / 60)" precision=0>h
<@CALC "(<@COLUMN 'DATEDIFF(mi,date_created,date_ended)'>-(@@days *
1440)) % 60">m
</@IF>
</@IF>
Explanation:
My database (SQL Server) contains 2 timestamps, date_created and date_ended. I
use the SQL Server DATEDIFF() to provide me with the number of minutes between
those 2 timestamps. You can calculate the same difference with <@CALC
‘<@TSTOSECS <@COL ts_start>> - <@TOTOSECS <@COL ts_end>>’> (this is seconds,
divide by 60 for minutes.
Then I use that value in the above code to draw either “Zm” or “Yh Zm” or “Xd
Yh Zm” (where X = number of days, Y = number of hours and Z = number of
minutes).
I treated this as if it was a formatting task, so the draw is inline and it
doesn’t handle seconds, but this could be easily reworked into a simple object.
Let me know if you’d like some help with that.
No matter what, you’ll need to something like the above to get the day count,
but to get at HH:MM:SS you can take you seconds value and pass it through
<@SECSTOTS> - this will give you a timestamp where the date is near 1/1/1970,
but the time is accurate – so <@SECSTOTS ###### format=datetime:%m/%d/%Y> would
actually be correct.
Robert
From: WebDude [mailto:[email protected]]
Sent: Monday, July 16, 2012 10:23 AM
To: [email protected]
Subject: RE: TeraScript-Talk: date calculations
Thanks for the help!
Let's forget about the weekdays vs. weekends for now. Any way to convert total
seconds into Days/Hours/Minutes effectively? Seems to me this would be
relatively easy to do, but I am stuck. I thought I saw something like this
before on the list, but cannot find it.
Thanks!
_____
From: Robert Shubert [mailto:[email protected]]
Sent: Saturday, July 14, 2012 11:39 PM
To: [email protected]
Subject: RE: TeraScript-Talk: date calculations
I think would do this in Witango.
Just giving this a quick thought, my approach would be like this:
Get the week number for both timestamps (format=datetime:%w) and then subtract
them, if the result is 0, then both days are in the same week and you can just
calculate the span (assuming that both are already weekdays). If the result is
1, then they are in consecutive weeks and you just need to calculate the time
from the startdate to Saturday 00:00:00 and then from Sunday 23:59:59 (of the
next week) to end date and add. If the number is greater than 1, you do the
same as if it was 1, but then also add 5 days for every 1 over 1, because the
are 5 days in each intervening week.
If it’s possible for the dates to be on the weekend already, then you should
slide them to the appropriate timestamp first (start to Monday AM and end back
to Friday PM)
There is no code to compensate for holidays. I’ve always wanted to add that
feature, but it’s daunting.
Let me know if this helps you get anywhere, there’s still some work to be done
to calculate the partial week spans.
Robert
From: WebDude [mailto:[email protected]]
Sent: Saturday, July 14, 2012 2:24 PM
To: [email protected]
Subject: TeraScript-Talk: date calculations
I have 2 timestamps in the db, we can call them starttime and endtime for now.
Has anyone ever wrote a down and dirty calculation that doesn't include weekend
days (i.e saturady and sunday? Holidays? I would settle for just a weekend day
omit for now. I have been farting around with this and ended up with a bunch of
code, but I was hoping for something more elegant. In other words, I am trying
to return the total time between the 2 dates and the average time between the
two dates for the total return. I started by writing direct db actions to the
db but it is pretty daunting for me. Going from datetosecs is relatively easy
to figuring this out. Trying to omit Saturaday and Sunday gets a bit more
complex, especially if you are dealing with hundreds of returns.
Witango 2000 for now... no Robert, I don't have the new servers up and running
yet. Hopefully soon!
;-)
WebDude
_____
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe terascript-talk" in the body.
_____
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe terascript-talk" in the body.
_____
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe terascript-talk" in the body.
----------------------------------------
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe terascript-talk" in the body.
