Jump to content

Re-code a number with PHP, from SQL


Butterbean

Recommended Posts

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 by Butterbean
Link to comment
Share on other sites

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 by ginerjm
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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;
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.