• 10 dec 2017: forum version update. In case of issues use this topic.
  • 30 nov 2017: pilight moved servers. In case of issues use this topic.
Hello There, Guest! Login Register


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
function: DATE_ADD
#1
The wiki states that the DATE_ADD function requires a datetime device, or a date/time string in the format
Code:
"YYYY-MM-DD HH:MM:SS"

I need to compare a calculated time to the actual time of day, regardless of the date.

Will the function work safely and correctly if I omit the date part like:

Code:
DATE_ADD("21:00:00", + RANDOM(0, 210) MINUTE)

In other words will the function use the current date if the date part is omitted?

Next, I need to compare the result of the function to the current time, but the datetime protocol only has numeric fields and the comparison would need a date/time stringUnsure

Finally, shouldn't the operator be "IS" instead of "==" because we are comparing strings?
 
Reply
#2
Can you write a pseudo rule of everything included?
 
Reply
#3
The best way to explain how I am using time calculations and randomiziation is by showing one of my current rules.
I created an extended version of the switch action that accepts a random range for the FOR option that I have sucessfully used during the last few weeks (doing the same for AFTER would be easy).

Code:
"IF vakantie.state IS on AND kantoor.state IS off AND (datetime.second % 10) == 0 AND datetime.time == (21.00 +t (0 ~ 120)) THEN switch DEVICE kantoor TO on FOR 5 ~ 60 UNIT 60"

The (datetime.second % 10) == 0 is only there to solve the issue that the rule otherwise was not executed and for clarity I will leave that out.

The result of replacement of the operators by the DATE_ADD and RANDOM functions would look like:

Code:
"IF vakantie.state IS on AND kantoor.state IS off AND current_datetime_string IS DATE_ADD("21.00",  +RANDOM(0, 120) MINUTE) THEN switch DEVICE kantoor TO on FOR RANDOM(5, 60) MINUTE"

The optimal solution would be however to have the current time available as a number (like in my newdatetime protocol) and to allow randomization for both FOR and AFTER:

Code:
"IF vakantie.state IS on AND kantoor.state IS off AND datetime.time == 21.00 THEN switch DEVICE kantoor TO on AFTER RANDOM(0, 120) MINUTE FOR RANDOM(5, 60) MINUTE"

The DATE_ADD function wouldn't even be required in this case Confused

Looking at my own working rules I found one more issue: calculations with sunrise and sunset times. These also are numbers and not strings, so cannot be used as a parameter for the DATE_ADD function nor can be compared to the result of that function.

I am not trying to push my operatorsSmile, functions that allow to achieve the same goal are fine, but because all date- and time values in pilight are numbers I personally think that a string based DATE_ADD function is not an adequate solution here.
Of course one can think of adding a MKTIME function and/or FORMAT functions, but that would just make rules more complicated.
 
Reply
#4
Function return values are not numbers or string by default. This depends on what is returned. pilight checks the parsed values for numbers or strings. DATE_ADD will always return a string, but DATE_FORMAT doesn't:

Number:
Code:
DATE_FORMAT(datetime, %H%M%S)

String
Code:
DATE_FORMAT(datetime, "%H%M%S")

String
Code:
DATE_FORMAT(datetime, %c)

DATE_ADD can be turned into a number with DATE_FORMAT:
Code:
DATE_FORMAT(DATE_ADD(datetime, +1 HOUR), \"%Y-%m-%d %H:%M:%S\", %H.%M)

Your final rule is not yet possible. It will be in the final version.
 
Reply
#5
The wiki didn't say anything about the DATE_FORMAT function, so I didn't know that it existed.
I will try to convert my time based rules using both functions and see what that brings.
 
Reply
#6
I thought you read the commit messages :p I have to write that page still. Examples of the new functions can be already found here though: http://wiki.pilight.org/doku.php/examples The input variables are the same as strftime. So you can lookup that manual.



DATE_FORMAT wiki page created.



You know what's funny. I wanted to implement this feature today:
Code:
DATE_FORMAT(DATE_ADD(2015-01-01 21:00:00, RANDOM(0, 120) MINUTE), \"%Y-%m-%d %H:%M:%S\", %H.%M)
seems it already works Big Grin



This rule now works as well:
Code:
IF vakantie.state IS on AND kantoor.state IS off AND DATE_FORMAT(datetime, %H.%M) == 21.00 THEN switch DEVICE kantoor TO on AFTER RANDOM(0, 120) MINUTE FOR RANDOM(5, 60) MINUTE
 
Reply
#7
Hi all,

I am always getting an error when trying to start this formular.
Code:
"alarm_light1": {
"rule": "IF sunriseset.sunset == DATE_FORMAT(DATE_ADD(datetime, -1 HOUR), \"%Y-%m-%d %H:%M:%S\", %H.%M) AND Kueche.state IS off AND datetime.second == 0  THEN switch DEVICE Kueche TO on",
"active": 1

Error:
Code:
[Dec 16 21:48:03:292062] pilight-raw: ERROR: DATE_ADD requires the datetime parameter to be formatted as "%Y-%m-%d %H:%M:%S"

Any ideas? It is in pilight version 7.0

Thanks.
 
Reply
#8
"datetime" is invalid in this contect. It is the name of a protocol, not of a device. You should create a datetime device to use here
 
Reply
#9
(12-17-2015, 09:35 AM)Niek Wrote: "datetime" is invalid in this contect. It is the name of a protocol, not of a device. You should create a datetime device to use here

Thats it perfect! I changed "datetime" to Zeit (name of my datetime device) now it is working.

Thank you.
 
Reply
  


Possibly Related Threads...
Thread Author Replies Views Last Post
  DATE_FORMAT & DATE_ADD heural 7 2,542 12-22-2016, 10:21 PM
Last Post: curlymo
  Timer function curatio 4 2,475 08-03-2015, 08:32 PM
Last Post: curatio
  Function "EVERY" needed Alex 2 1,569 06-02-2015, 08:57 AM
Last Post: terrar

Forum Jump:


Browsing: 1 Guest(s)