Mysql – Giving a name to a dynamic column

MySQL

I have written a query to fetch a time internal:

select *, (Time + interval '2012-12-18 13:36:47' minute)  from chatnotiservice;

Is it possible to name the (Time + interval '2012-12-18 13:36:47' minute) column?

Best Answer

Use AS. For example:

mysql> select 'data' as mycolumnname;
+--------------+
| mycolumnname |
+--------------+
| data         |
+--------------+
1 row in set (0.00 sec)

mysql>

If you actually want to name the column after the date operation you're performing (your question isn't clear), you can do it by enclosing it in backticks. For example:

mysql> select 'data' as `Time + interval '2012-12-18 13:36:47' minute` ;
+----------------------------------------------+
| Time + interval '2012-12-18 13:36:47' minute |
+----------------------------------------------+
| data                                         |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql>

I suspect what you mean is the following:

select *,(Time + interval '2012-12-18 13:36:47' minute) AS mytime from chatnotiservice;

Related Solutions

Mysql – Finding five-minute interval prior to current time

Here is the query that will give you the most recent 5 minute interval

select
    fm_ending - interval 5 minute fm_beg,fm_ending
from 
    (
        select gt
            - interval mod(minute(gt),5) minute
            - interval second(gt) second fm_ending
        from
            (select now() gt) giventime
    ) A
;

To test it, insert anything you want for gt

Example : You mentioned '2012-03-11 08:43:06'

mysql> select
    ->     fm_ending - interval 5 minute fm_beg,fm_ending
    -> from
    ->     (
    ->         select gt
    ->             - interval mod(minute(gt),5) minute
    ->             - interval second(gt) second fm_ending
    ->         from
    ->             (select '2012-03-11 08:43:06' gt) giventime
    ->     ) A
    -> ;
+---------------------+---------------------+
| fm_beg              | fm_ending           |
+---------------------+---------------------+
| 2012-03-11 08:35:00 | 2012-03-11 08:40:00 |
+---------------------+---------------------+
1 row in set (0.02 sec)

mysql>

Give it a Try !!!

MySQL – Optimized Query to Fetch a Record from 10 Million Entries

For the table mydb.mytable with UniqueKey and timeStamp, to see if the UniqueKey exists within the last 5 minutes, simply run this

SELECT COUNT(1) FROM mydb.mytable
WHERE UniqueKey = ????
AND timeStamp >= ( NOW() - INTERVAL 5 MINUTE );

SELECT COUNT(1) FROM mydb.mytable
WHERE UniqueKey = ????
AND timeStamp >= ( NOW() - INTERVAL 300 SECOND );

What does the value indicate ???

If you get 0, the UniqueKey is older that 5 minutes
If you get 1, the UniqueKey is within the 5 minutes

Make sure the UniqueKey has a unique index.

Give it a Try !!!

Since your timestamp is a UNIX timestamp, I'll adjust the code using UNIX_TIMESTAMP() function

SELECT COUNT(1) FROM mydb.mytable
WHERE UniqueKey = ????
AND timeStamp >= UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE);

Best Answer

Related Solutions

Mysql – Finding five-minute interval prior to current time

MySQL – Optimized Query to Fetch a Record from 10 Million Entries

Give it a Try !!!

Related Question