CODEWITHSUNDEEP
mysqldatetime
Gravy
Gravy

Reputation: 12445

MySql timestamp rounding then reformatting in human readable format

I have the following query from Group OHLC-Stockmarket Data into multiple timeframes - Mysql.

SELECT
  FLOOR(MIN(`timestamp`)/"+period+")*"+period+" AS timestamp,
  SUM(amount) AS volume,
  SUM(price*amount)/sum(amount) AS wavg_price,
  SUBSTRING_INDEX(MIN(CONCAT(`timestamp`, '_', price)), '_', -1) AS `open`,
  MAX(price) AS high,
  MIN(price) AS low,
  SUBSTRING_INDEX(MAX(CONCAT(`timestamp`, '_', price)), '_', -1) AS `close`
FROM transactions_history -- this table has 3 columns (timestamp, amount, price)
GROUP BY FLOOR(`timestamp`/"+period+")
ORDER BY timestamp

In my select statement, FLOOR(MIN(timestamp)/"+period+")*"+period+" AS timestamp,

  1. I am trying to understand what it is doing. and
  2. I need to convert this back to a mysql date/time Y-M-D H:i:s string or a UTC timestamp for parsing via javascript.

Let's assume that +period+ is 86400 (The number of seconds in a day) Let's assume that the timestamp is '2015-12-08 20:58:58' From what I can see, it takes the timestamp, which internally is stored as an integer and divides by 86400.

'2015-12-08 20:58:58' / 86400 = 233231576.4566898000

It then uses the FLOOR operation which would make it 233231576 then multiplies by 86400 again (I assume that this is to ensure rounding to the day)

I end up with 20151208166400.

So that's the 8th December 2015 but I also have 166400 which I have no idea what it is?

So now the second part of the question is, how to convert this integer to 2015-12-08 %H:%i:%s or even a UTC timestamp for parsing via Javascript.

Upvotes: 1

Views: 402

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

I mentioned the problem in the comment, but not a fix. The problem is that the proposed code is for a unix timestamp, not a datetime value.

This can be fixed by doing appropriate conversions

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(MIN(timestamp)) / $period) * $period)

This gives you the flexibility of have arbitrary numbers of seconds for the groupings.

Upvotes: 1

Suraj Gahatraj
Suraj Gahatraj

Reputation: 1

Convert a Date object to a string, according to universal time:

var d = new Date();
var n = d.toUTCString();

The result of n will be:

Mon, 28 Dec 2015 12:57:32 GMT

Upvotes: 0

qser
qser

Reputation: 1

  1. It is rounding timestampt to period (e.g day).
  2. DATE_FORMAT( DATE( FLOOR(MIN(timestamp)/"+period+")*"+period+" ) , '%Y-%m-%d %H:%i:%s' )

If period==day consider using only MySQL period rounding by DAY().

Upvotes: 0

O. Jones
O. Jones

Reputation: 108676

You're right that FLOOR(timestamp / 86400) * 86400 is a crude way of rounding a UNIX-style timestamp (seconds since 1970-01-01 00:00UTC) to midnight on the present day UTC.

If that's what you're trying to do, I suggest you try this kind of MySQL code:

 SELECT DATE_FORMAT(DATE(`timestamp`), '%Y-%m-%d'),
        ...
  GROUP BY DATE(`timestamp`)

This uses MySQL's built in date arithmetic to turn a timestamp into midnight.

But you should be careful of one thing. Those timestamps are all stored in UTC (f/k/a Greenwich Mean Time). When you do date arithmetic with them, or pull them out of the database to use them, they're automatically converted to local time according to your MySQL time zone settings.

Upvotes: 0

Related Questions