That's closer:
SELECT
UNIX_TIMESTAMP() + 86400 - (UNIX_TIMESTAMP() % 86400);
Gives me 6:00 PM today...
On 03/01/2011 12:32 PM, Singer X.J. Wang wrote:
SELECT
unix_timestamp() + 86400 - (unix_timestamp() % 86400);
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/m
PM, Singer X.J. Wang wrote:
http://en.wikipedia.org/wiki/Midnight
On Tue, Mar 1, 2011 at 13:00, Singer X.J. Wang <mailto:w...@singerwang.com>> wrote:
That's cause there's two midnights.. use
= 1298999201 + 86400 + (1298999201 % 86400)
On Tue, Mar 1, 2011 at 12
It was of course a typo, and even with the correct number isn't the answer
On 03/01/2011 11:47 AM, Claudio Nanni wrote:
You can start by using 60*60*24=86400
;)
On Mar 1, 2011 6:17 PM, "Bryan Cantwell" <mailto:bcantw...@firescope.com>> wrote:
> I'd as
I'd asked before how to convert a unix timestamp to the hour that it is
in (and got the perfect answer) :
1298999201 = 3/1/2011 11:06:41 AM
(1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM
Now getting the timestamp converted to midnight of that same day isn't
as simple as:
1298999201
Yes perfect! Thanks, I knew I was over thinking this.
On 02/24/2011 10:56 AM, Nathan Sullivan wrote:
Bryan,
Maybe something like this would work?
select 1296158500 - (1296158500 % 3600)
Hope that helps,
Nathan
On Thu, Feb 24, 2011 at 08:41:58AM -0800, Bryan Cantwell wrote:
How would I go
How would I go about modifying a unix timestamp to actually represent
the 'top of the hour' that it represents?
For instance:
1296158500 = 1/27/2011 2:01:40 PM
That is in the 2:00 pm hour, how can I find that out and modify it to
1296158400 which = 1/27/2011 2:00:00 PM?
--
MySQL General Mai
I have an innodb table with 200 million rows and growing.
It is a highly active table with tons of inserts and updates at all times.
I notice a select query I test on that table is 0.01 seconds or less
when all the inserts/updates are OFF.
But when I throttle up the writes to the table, the selec
work in spite of what
the manual says.
On Fri, 2010-10-01 at 15:42 -0700, Daevid Vincent wrote:
> Did you even look at the manual?
>
> http://lmgtfy.com/?q=mysql+set+timezone
>
> First link.
>
>
> > -Original Message-----
> > From: Bryan Cantwell [mailto:b
Any way to change timezone WITHOUT mysqld restart?
It would be a lifesaver if there were some way for me not to have to
restart because if mysql restarts then I have to go through a lot of
other issues with my other apps.
I'm using mysql 5.0.51a (yes I know it's old but I cannot upgrade at the
moment) on linux.
My challenge is that mysql admin does not wait long enough for mysql
database(s) which are all innodb and VERY large (billions of rows) and
eventually just kills the db.
This causes innodb to try and repair
I have a simple table
mytable
id int
time_stamp tiemstamp
value int
tid int
I want to find the max id for each individual tid in my table.
if I did it one by one it would be:
select max(id) from mytable where tid = 12345;
but how can I do something more like:
select concat(tid,':',id) from my
Yes, but I DON'T want eh spikes smoothed out
On Fri, 2010-08-20 at 17:16 +0200, Jangita wrote:
> On 20/08/2010 5:12 p, Bryan Cantwell wrote:
> > I am trying to produce charts for large amounts of data. I already limit
> > the user to a smaller time frame in order to redu
I am trying to produce charts for large amounts of data. I already limit
the user to a smaller time frame in order to reduce the possible data
points, but still can end up with far more data points than are clearly
plottable on a chart. Does anyone have an idea of how I can drop
insignificant poin
I know that you can ignore certain databases and tables in mysql
replication, but is it possible to replicate all but a certain column or
two from a table? This is 5.1.48 on linux.
Thanks,
Bryancan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
I have a very large table (over 2 billion rows) which is partitioned by
day. I'd like to archive all the data in the oldest partition to another
table before I drop that partition. Is there some way other than a
simple select statement that has parameters that match those of the
partition whic will
I have a stored procedure in mysql 5.1.48 that deletes old data from my
tables.
I would like to keep a running count while it does this. Here is what I
try now:
...
DECLARE dropCnt INT DEFAULT 0;
...
SET @sql = CONCAT('DELETE FROM myTable WHERE itemid = ', iID, ' AND
clock BETWEEN 0 AND ', histUni
I have to delete old records from a very large table (1.6billion rows)
in a stored procedure.
CREATE TABLE mytable(
id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
unix_time INT(11) NOT NULL DEFAULT 0,
value DOUBLE (20, 4) NOT NULL DEFAULT 0.,
UNIQUE INDEX history_1 USING BTREE (id, unix_ti
Is there a benefit to a combined index on a table? Or is multiple single
column indexes better?
If I have table 'foo' with columns a, b, and c. I will have a query
like:
select c from foo where a in (1,2,3) and b < 12345;
Is index on a,b better in any way than an a index and a b index?
An explain
Perhaps someone has already accomplished this:
I have a simple table with 3 columns:
mytable(
myid BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
myunixtime INT(11) NOT NULL DEFAULT 0,
myvalue BIGINT(20) UNSIGNED NOT NULL DEFAULT 0
)
It is collecting millions of rows.
The myunixtime column is a un
Anyone have information they can provide on the performance hit of using
innodb_file_per_table?
I'd assume that since there are many individual tables that this would
slow performance, but perhaps not.
In a huge database, is this not a good idea, or a better one?
--
MySQL General Mailing List
I have a stored proc I need to call from yet another stored proc, which
then needs to use the results from the called proc ... I could probably
use temp table but really dont want to do that unless absolutely
required. I cant use OUT parameters, because the called stored proc
returns multiple r
Does anyone have a suggestion on how a database link (like in Oracle)
could be established between two Mysql databases on different servers?
It would be awesome if I could write sql that will query both databases
in one query...
--
MySQL General Mailing List
For list archives: http://lists.mys
I have an environment where upon boot of a machine I need to know if
mysql shutdown nicely or if it crashed.
How can I know for sure which was the case so that I can take action if
needed?
I notice that issuing a reboot or shutdown -r now command, (in Linux)
that the 'service mysql stop' is nev
...@eds.com
Pager mail:9884430...@nma.vodafone.in
-Original Message-
From: Bryan Cantwell [mailto:bcantw...@firescope.com]
Sent: Wednesday, October 14, 2009 8:40 PM
To: mysql@lists.mysql.com
Subject: Mysql service will not stop
I have mysql 5.0.51a running on a linux 2.6.26 box. The service
I have mysql 5.0.51a running on a linux 2.6.26 box. The service my6sql
stop command fails and all that appears in the log or err log is the
following... what can I do to repair this?
091014 17:00:24guardian: instance 'mysqld' is running, set state to
STARTED.
091014 17:07:40The instanc
easy to detect and repair with a daemon script. Even if both
machines die, it'll be a similar scenario.
Regards,
Gavin Towey
-Original Message-----
From: Bryan Cantwell [mailto:bcantw...@firescope.com]
Sent: Wednesday, October 07, 2009 10:47 AM
To: mysql@lists.mysql.com
Subject: R
I have 2 - 5.0.51a mysql databases setup in a dual master scenario. A is
master of B and vise versa...
In Linux 2.6.26 (if that matters).
Everything is great while all is running normally. But, when I am
testing the system by creating disasterous scenarios, I find some
challenges I hope to get
Is there no way to step thru a stored proc in order to debug it and see
what it is doing?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I need to create a cursor using dynamic sql. Below is a short example of
what I am trying to use in order to get the correct resultset... ignore
the rest of the proc, I just need to know how to make the cursor
declaration from @vsql
Thanks
DELIMITER $$
DROP PROCEDURE IF EXISTS `firescope`.`trigg
on for your distribution on how to do that.
Number of processes running now: 0
From: Dan Rogart [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 13, 2008 12:27 PM
To: Bryan Cantwell; mysql list
Subject: Re: Crashed InnoDB
Have you tried starting mysqld with innodb_force_r
No input on this one?
-Original Message-
From: Bryan Cantwell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 12, 2008 11:51 AM
To: mysql@lists.mysql.com
Subject: Crashed InnoDB
We had a power outage, now the mysql wont start at all. Here is the err file
output... Any help on how to
We had a power outage, now the mysql wont start at all. Here is the err file
output... Any help on how to recover?
080212 11:35:50 mysqld started
080212 11:35:50 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd fi
I have Mysql 5.0.45 using innodb tables.
Occasionally, I get corrupted tables. I can go into Mysql administrator
gui and see the bad table and I can repair the index or whatever is
wrong from the gui.
I need a command line way to periodically detect for issues and if it
finds one the I need a comma
I have the following horrible sql. I need one result that has all the
data in one row. I am currently using 3 sub queries and figure it must
be a better way...
SELECT 'FS_DEV',
ifnull(a.severity, 0) AS aseverity,
ifnull(a.
I have the following proc... when I run it I get a response that says
"You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'NULL' at
line 1".
I just want a programatic way to upgrade db engine to innodb where I
don't
I see no string to date function that does this conversion...
-Original Message-
From: Bryan Cantwell
Sent: Monday, May 21, 2007 2:08 PM
To: MySQL General
Subject: string to timestamp conversion
I have a table with a varchar column that contains a timestamp like
this: 'Thu May
I have a table with a varchar column that contains a timestamp like
this: 'Thu May 17 09:15:47 2007'
I need to grab this and include it in an insert sql that puts that value
in a table as a timestamp...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
I have a need to output a recordset that shows the record with the
higest value for severity within a date range. That is easy enough, but,
in the same query, I need to show that data 3 times in the same query.
Once where event_avail = 1, then again where event_perf = 1 and finally
where even_sec =
day, March 03, 2007 1:40 PM
To: Bryan Cantwell
Cc: mysql@lists.mysql.com
Subject: Re: Need sql optimization help
Bryan,
A 'Not Exists' query
<http://www.artfulsoftware.com/infotree/queries.php#41> is usually
faster when coded as an exclusion join, eg for max-some-value per key,
left
I have the following sql that works for what I want to see but is
terribly slow due to the subquery. Any suggestions on how to get same
result faster? I have tried group by and cant seem to get the correct
results that way
Also is there a product that can help optimize sql and indexing?
SELEC
055 06:01 AM0.02
2/24/2007 06:02:02055 06:02 AM0.08
Bryan Cantwell
Director, Development
(M) 469.361.1272
[EMAIL PROTECTED]
www.FireScope.net
Trying to migrate to MySql 5 from Sybase asa. I have a tables that have
column values that are calculated based on other columns in the table. How can
I accomplish the same in MySql?
Here is sample of my asa table:
CREATE TABLE "DBA"."OpptyDetail"
(
"OpptyDetailID"
42 matches
Mail list logo
