Category Archives: MySQL

Javascript, UTC datetime and formatting oh my…

Leave a reply

I’ve been working on a project to display data stored in a MySQL database using the Google Graph API.  I’ve done this before, but this time I need to display the time the MySQL entry was created.  The DB row contains this information as a MySQL timestamp field, and is returned as a string when I fetch it, for example:

2017-07-09 02:30:00

This is in UTC, and as I’ve discovered, the PHP script has no idea of what timezone the browser is in, so either I need to pass that from JavaScript, or hand the UTC times back to the browser and let it take care of the conversion.  I decided it would be best to let the browser handle it (for now), and immediately discovered the confusion around formatting a Date string with JavaScript.

To me, this seems pretty basic, and I really don’t understand why it was omitted from the language.  There are a lot of libraries out there that will do this for you, but I ended up using jQuery, as I was already using it for the AJAX and graphing support.

jQuery provides the .datepicker class, which includes a date formatter.  I just needed the month and day, the month needs to be a short string, while the day is a number without any leading zeros.  I used the following:

$.datepicker.formatDate("M-d",utcTime)

Where utcTime is a Date object that’s been created from the MySQL string with:

var dt = new Date(Date.parse(item[0] + "Z"));

I append the Z to the MySQL string, so JavaScript knows its a UTC time.

At the time I did this, there is no option for handling Time within the standard jQuery package, so I added that manually, padding with a leading ‘0’ as needed, with the final date/time string being created with:

return $.datepicker.formatDate("M-d",utcTime) + " "
        + ("0" + utcTime.getHours()).slice(-2) + ":"
        + ("0" + utcTime.getMinutes()).slice(-2);

Note: I could have had .datepicker parse the string, but it wouldn’t have given me the time portion, so I do that in a separate step.

MySQL and ON DUPLICATE KEY UPDATE

Leave a reply

I’ve been working on some MySQL stuff recently, specifically storing a snapshot of the price of bitcoin every 5 minutes from an online exchange.  As you can imagine, the amount of data this can generate is quite large, and becomes more than I need.  I mean, I don’t care what the price for Bitcoin was exactly 3 weeks, 2 days, 11 hours and 20 minutes ago was.  I do care about averages and trends though.

So, I decided to aggregate my data to hourly averages, along with a min/max for each hour.  Then I can delete any of the 5 minute interval data older than a week or so, and use the aggregated data to do my analysis and graphs.

In order to do this I wanted to run a query that would average all the entries within the current hour, and then update a row with these updated averages, along with min/max values.  I also wanted the query to insert a new row if it was a new hour.  After a little research I knew I needed to figure out INSERT INTO… ON DUPLICATE KEY UPDATE as my pattern.

Everything seemed to be going great, until I needed to store calculated fields in an updated row.  I couldn’t get the SQL statement to actually update the row.

It turns out, when you use a SELECT to generate the data to be INSERTed, MySQL creates a temporary table with the data in it, and I couldn’t find the name of the table, so trying to update the values with entries from the SELECT would fail with ‘No such column’ error.

I fixed this by storing the calculated results in specific variables, and in turn using those variables to update the existing row.

INSERT INTO tickerHourly
 (ID, time, product_id, priceAvg, priceMax, priceMin, volumeAvg, counter)
 SELECT
 (select ID from tickerHourly WHERE product_id = ticker.product_id 
 AND hour(time) = hour(UTC_TIMESTAMP()) and date(time) = date(UTC_TIMESTAMP())),
 FROM_UNIXTIME(60*60*ROUND(UNIX_TIMESTAMP(time)/(60*60))),
 product_id,
 @avg := AVG(price),
 @max := MAX(price),
 @min := MIN(price),
 @volumeAvg := AVG(volume),
 @counter := count(*)
 FROM ticker where hour(time) = hour(UTC_TIMESTAMP())
 AND date(time) = date(UTC_TIMESTAMP())
 AND product_id = ?
 group by date(time),hour(time),product_id
 ON DUPLICATE KEY UPDATE
 tickerHourly.counter = @counter,
 tickerHourly.priceAvg = @avg,
 tickerHourly.priceMax = @max,
 tickerHourly.priceMin = @min,
 tickerHourly.volumeAvg = @volumeAvg;

So the line ‘@avg := AVG(price)’ takes the average of all the price entries and stores it in the local variable avg, which I then use as part of the UPDATE clause.  This was my missing information.