Re-code a number with PHP, from SQL

[Butterbean]

Suppose I have a series of numbers being stored in an SQL table, which are not uniform and constantly growing.  Once the numbers in the table (in sql) hit 99,999,999, they start back at 1, and do it all over again continuously until the end of time.

 

If I am performing calculations in PHP on these numbers (stop date-start date), is there a function that can take a look at the next cell after 99,999,999 and acknowledge that the 1 is really 100,000,000.  

 

In the below example, instead of using 99,999,999, we used 4 as the last number before numerical reset. Once the numbers hit 4, they reset to 1. continuously. 

 

Example1.  If I were to subtract Jan 1(start) from jan 4(stop) I would have Stopdate -Startdate = 4-1 =3.

 

Example2.  If I were to subtract Jan 4(start) from jan 7(stop) I would have Stopdate-Startdate = 3-4 =-1 but should be 7-4 for the sake of the calculation only.

 

Example3.  If I were to subtract Jan 8(start) from jan 11(stop), I would have Stopdate-Startdate = 3-4=-1 but should be 11-8=3 but for ease of not having to be a continuous repeating thing, can be represented at 7-4.  There is no need to keep a running memory towards infinity.  It just needs to know that any number after some number ending in 4 is 5, instead of reseting to 1.  

 

 

jan 1 2014.......1

jan 2 2014.......2

jan 3 2014.......3

jan 4 2014.......4

jan 5 2014.......1

jan 6 2014.......2

jan 7 2014.......3

jan 8 2014.......4

jan 9 2014.......1

jan10 2014......2

jan11 2014......3

 

Hope this is not that confusing.

 

Long story short, I can't do a stopdate-startdate = somenumber, if that some number keeps resetting to 1 once it reached 99,999,999.  As a result, my code will work perfectly until I reach 99,999,999, then I am in trouble because 1-99,999,999 is not equal to 1, where 100,000,000 -99,999,999 = 1

 

 thanks. 

Edited October 24, 2014 by Butterbean

[jcbones]

Is this for dates only?  If so, you should be storing in a date, datetime, or timestamp column.  Mysql has a vast amount of functions for working with dates, but the column has to be the right format.

[ginerjm]

I agree with jcjones .  You propose a table full of numbers but then give only examples of date arithmetic.  Whether you know it or not, dates CAN be stored as numbers which there are functions for handling.  Perhaps a little reading is in order here before you proceed.

Edited October 24, 2014 by ginerjm

[Psycho]

I really have no clue what you are asking. Your explanation seems to have taken a 180% turn halfway through. I understand what you were talking about with wanting a numerical value that repeats after 99,999,999 (although it probably isn't necessary).

 

Then you go on to an example using 4 as the repeat limit, but the things with the dates doesn't make any sense whatsoever. You discuss using two dates where the difference is used to come up with this number (which was supposedly incrementing up to 4). But, what is the significance of the dates? The dates are going to be whatever they are so, the value is whatever the value is. If the difference between those dates will be incrementing accordingly then it works - else it does not. So, it seems as if the dates used are what need to be 'manipulated'.

 

 

 

Example3.  If I were to subtract Jan 8(start) from jan 11(stop), I would have Stopdate-Startdate = 3-4=-1 but should be 11-8=3 but for ease of not having to be a continuous repeating thing, can be represented at 7-4.  There is no need to keep a running memory towards infinity.  It just needs to know that any number after some number ending in 4 is 5, instead of reseting to 1.  

Um, what? How is the difference between Jan 8 and Jan 11 represented as 3-4=1??? The difference will be three (either negative or positive).

 

 

In any event, I think you want the modulus operator. With this you can have an infinite "base" number that results in a repeating series with a maximum you define. The modulus is the Remainder from a division. For example, 10 / 4 = 2 with a remainder of 2. So, the modulus is 2. The cool thing is it results in the ability to create a numerical repeating series. The modulus is represented using a percentage symbol - %. Using the limit of 4 as in your above example we can create a repeating series of four - however the numbers will be form 0 to 3.

 

 

1 % 4 =  (1 / 4) = 1
 2 % 4 =  (2 / 4) = 2
 3 % 4 =  (3 / 4) = 3
 4 % 4 =  (4 / 4) = 0
 5 % 4 =  (5 / 4) = 1
 6 % 4 =  (6 / 4) = 2
 7 % 4 =  (7 / 4) = 3
 8 % 4 =  (8 / 4) = 0
 9 % 4 =  (9 / 4) = 1
10 % 4 = (10 / 4) = 2

 

You could simply add 1 to the modulus result to get the series to be the range of 1 - 4. You could use the modulus on the value before you store it in the DB or can simply let the number stored to the DB be infinite and use the modulus after you retrieve it. You really didn't provide enough information to understand how you are using this to make a good suggestion.

[Butterbean]

Is this for dates only?  If so, you should be storing in a date, datetime, or timestamp column.  Mysql has a vast amount of functions for working with dates, but the column has to be the right format.

<?php
$sql = ";WITH TOTAL_KWH_SUMMER AS
(
SELECT CONVERT(VARCHAR(10),cdate,111)AS trans_date, datepart(hh, cdate) as trans_hour, comm_id, 
MIN((total_energy_a+total_energy_b+total_energy_c)/100) AS minUsage, 
MAX((total_energy_a+total_energy_b+total_energy_c)/100) as maxUsage, repeated
  FROM [radiogates].[dbo].[purge_data] 
  where comm_id='$comm_id' 
  group by comm_id, CONVERT(VARCHAR(10),cdate,111), datepart(hh, cdate), repeated
)
SELECT *, datepart(weekday, trans_date) as trans_date_day, datepart(month, trans_date) as 

trans_date_month, maxUsage - minUsage as totalUsage
FROM TOTAL_KWH_SUMMER 

where datepart(weekday, trans_date) IN ('1', '2', '3', '4', '5', '6', '7') 
AND DATEPART(MONTH, trans_date) IN ('5','6','7','8','9','10') 
and trans_date BETWEEN '$startdate2 00:00:01' AND '$enddate2 24:00:00' and repeated <> '1' ";
$query = sqlsrv_query($conn, $sql);if ($query === false){  exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row = sqlsrv_fetch_array($query)){  	$sumUsageKWH += $row[totalUsage];}sqlsrv_free_stmt($query);?> 

To answer your question, yes, the timestamps are in the table.   I have it filtering by above, down to the 15 minutes. We are using sql server 2012.  The table housing the data is [purge_data] listed above.  Im just not sure where to go.  Not asking for someone to do this work, just a direction to get me started.  Maybe even a small intro tutorial of sorts.

[Butterbean]

I agree with jcjones .  You propose a table full of numbers but then give only examples of date arithmetic.  Whether you know it or not, dates CAN be stored as numbers which there are functions for handling.  Perhaps a little reading is in order here before you proceed.

I understand what you are saying.  I provided a little more to go on.  As i mentioned, Im looking for basic direction only.  I'm thrown into this code and have to come up with a solution in 30 days.  Problem is I have enough knowledge to troubleshoot and that's about it.  I'm going to learn what I can and make it happen, just looking for a nudge. See post above.

[ginerjm]

So - maybe try re-explaining what your problem is since we are confused about what you have and what you want to do with it.

[Butterbean]

"Um, what? How is the difference between Jan 8 and Jan 11 represented as 3-4=1??? The difference will be three (either negative or positive)."

.....................................................................................................................................................................................................................

 

 

A query would never have to cycle through more than 1 time, so its not needed to cycle through twice as is below,( 2nd column of numbers after the dates)  

 

jan 1 2014.......1......1

jan 2 2014.......2......2

jan 3 2014.......3......3

jan 4 2014.......4......4

jan 5 2014.......1......5

jan 6 2014.......2......6

jan 7 2014.......3......7

jan 8 2014.......4......8

jan 9 2014.......1......9

jan10 2014......2......10

 

jan11 2014......3......11

 

 

Simply put, where ever the query starts, it would need to convert (so to speak) through only one (1) cycle.

 

In an actual example from my code posted in a previous response above, it takes several months for a customers kWh to reach the ongoing number of 99,999,999 (which is the recycle point).  As a result, there will never be a query that goes through two cycles of numbers (like above).  I was just attempting to say you only need to cycle one time.  Basically whenever the query starts, if it starts at 99,999,945 and ends at 10, it needs to obtain the values from the query and code it appropriately so that it understands, 10 represents 100,000,010 for the sake of performing the calculation of 100,000,010 - 99,999,945 = 65 kwh (kilowatt hours).  Otherwise, the value displayed is 10 - 99,999,945 =  -99999935 which is incorrect by the apps standards.  Thanks for helping.  

[Butterbean]

So - maybe try re-explaining what your problem is since we are confused about what you have and what you want to do with it.

See above post, i cleared it up.  Thank you for your help.  I'm sorry for my confusion.

[Barand]

so

$start_reading =  99999945;
$end_reading = 10;

$usage = $end_reading - $start_reading;
if ($usage < 0) {
    $usage += 100000000;
}
echo $usage;

[Psycho]

I would modify the end reading. That way if you need the start/end values in their actual values you will have them

 

$start_reading = 99999945;
$end_reading = 10;
 
if($end_reading < $start_reading) { $end_reading += 100000000; }

$usage = $end_reading - $start_reading;
echo $usage;

[ginerjm]

I'm more confused now than before.  Guess I'll move on.