Some advice on date/time tarriffs

Hey Guys,

Just writing up some routines for working out what electricity tarriff applies at a given time of a day.

We have the following tarriffs

Controlled Load 1/2

I receive a feed from my electricity provider that gives me the wholesale rate projected forward in 30 minute increments - on top of that wholesale rate we then have the tarriffs listed above added to the base wholesal rate.

I think the most efficient way would be to create an multidimensional array - but how would others approach it ?

The rates do get updated on a fairly frequent basis, and the dates they are in effect can be changed on a yearly basis

I was thinking of

Monthnumber[DayOfWeek].[HourOfDay].[ApplicableTarriff] as an obvious structure and then populate that array each day from a listed table

Is this the sane approach or is there a better way ? Such as a javascript object ?


Don't store timestamps as month, day, hour etc, just keep them as javascript timestamps (which are the number of milliseconds since the start of 1970, if I remember correctly). When it comes to looking up the data that will be hugely more efficient.

So to store the wholesale prices all you need is a timestamp marking the start of the 30 minute period and the rate for that period.

Do you need to be able to keep and look these up over an extended period? If so I store those in a database. Are you already using Influx, in which case put them in there. If not then an sqlite db should do the job. Then it is simple to extract the price for any time.

You could keep the time of day tariffs separately. I guess there is a possibility of these changing over a period of time in which case you will need a history of those too. Alternatively in the prices table above you could calculate the actual price including the tariff and store that, either instead of the wholesale price or as well as it.

Thanks Colin,

No all these prices and times are very transient - so no point in storing them in a DB.

Currently the prices are set every 5 minutes and bid on by the electricity generators, then averaged into 30 minute blocks.

As of the end of this year - this will move to a true 5 minute billing/generating price.

As such this query will be run every 5 minutes and it will return the results for a 24 hour period - but i will probably only use the next 12 hours worth.

So what happens is that the query is run and it returns the current 5 minute wholesale price, i then need to lookup month, day of week, and hour to determine what tarriff is currently applicable and to then add the appropriate rate for that period to the wholesale price that has been returned by the 5 minute query

So in terms of what i need to achieve -put aside the query from the internet as a secondary thing i need to be able to lookup (in a fairly efficient way) the current month, day of week and hour to return to me what tarriff is applicable for that particular moment in time


I like to use JSONata for time values, as i find it easier than JS. The below example will take a timestamp in payload and return a object in msg.times, of hour, day of week and month.

[{"id":"12cc2809.c067d","type":"change","z":"9b3f9f31.c45298","name":"","rules":[{"t":"set","p":"times","pt":"msg","to":"(\t   $timeKeys := [\"hour\",\"dayOfWeek\",\"month\"];\t   $merge(\t       $map(\t           $split(\t               $moment($.payload).tz(\"europe/London\").format(\"H-e-M\"),\t               \"-\"\t           ),\t           function($v,$i){{$timeKeys[$i]:$number($v)}}\t       )\t)\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":320,"wires":[["f0f44ae1.955ec8"]]},{"id":"39fc1e12.6fcaca","type":"inject","z":"9b3f9f31.c45298","name":"","props":[{"p":"payload"},{"p":"times","v":"{}","vt":"json"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":270,"y":260,"wires":[["12cc2809.c067d"]]},{"id":"f0f44ae1.955ec8","type":"debug","z":"9b3f9f31.c45298","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":540,"y":320,"wires":[]}]

I have been avoiding JSONata as it doesmy head in !! I guess this may be the one that tips me over the edge and forces me to come to grips with it !

Thanks for posting will check out the sample you have provided


OK here is where my thinking is at at the moment on this

I intend to create a number of arrays and then an array of arrays as such


I should then be fairly easily able to parse the current date and time to work out what is the current tarriff that is applicable - and to then also project forward a number of hours to work out what will be applicable.

These tarriffs can then be looked up in a seperate table and added to the variable wholesale electricity pricing.


I suggest a three dimensional array, where the value in each cell is the tariff. So to get the tariff when you know hour, day, month you just need to do
let tariff = tarriffs[month][day][hour]
then wrap it in a function which you call with a javascript Date object which works out the hour, day, month and returns the answer.

Thanks Colin, I think that was where i was bumbling along to !!


This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.