Re: Last established connection timestamp by a specific user

[Singer Wang]

Unfortunately not with the standard configuration.

You're best bet going forward would be to look at
MySQL Enterprise Audit -
https://www.mysql.com/products/enterprise/audit.html


On Tue, Oct 10, 2017 at 3:54 PM, Gone, Sajan <sg...@lb.com> wrote:

> Hi,
>
>We have a MySQL instance which is currently running on version
> `5.7.11-enterprise-commercial-advanced-log`.  On this instance I am
> trying to figure out the most recent timestamp at which a specific user has
> established a connection to this instance (or) performed any DML operations
> which might have changed the status of the database.
>
> Is there any way I can get such information from the 
> information_schema/performance_schema
> tables (or) from any of the mysql logs?
>
> Thank You,
> Sajan Gone
> Database Administrator.
>
> 
>
> Notice: This communication may contain privileged and/or confidential
> information. If you are not the intended recipient, please notify the
> sender by email, and immediately delete the message and any attachments
> without copying or disclosing them. LB may, for any reason, intercept,
> access, use, and disclose any information that is communicated by or
> through, or which is stored on, its networks, applications, services, and
> devices.
>

Last established connection timestamp by a specific user

[Gone, Sajan]

Hi,

   We have a MySQL instance which is currently running on version 
`5.7.11-enterprise-commercial-advanced-log`.  On this instance I am trying to 
figure out the most recent timestamp at which a specific user has established a 
connection to this instance (or) performed any DML operations which might have 
changed the status of the database.

Is there any way I can get such information from the 
information_schema/performance_schema tables (or) from any of the mysql logs?

Thank You,
Sajan Gone
Database Administrator.



Notice: This communication may contain privileged and/or confidential 
information. If you are not the intended recipient, please notify the sender by 
email, and immediately delete the message and any attachments without copying 
or disclosing them. LB may, for any reason, intercept, access, use, and 
disclose any information that is communicated by or through, or which is stored 
on, its networks, applications, services, and devices.

Re: DATETIME vs CHAR for "timestamp"

[SSC_perl]

> On Apr 14, 2017, at 1:07 PM, shawn l.green  wrote:
> 
> That all depends. Do you...

Hi Shawn,

I thought I had replied to your response, but it looks like I didn’t.  
Thank you for your email.  It was a thorough response and the links were very 
helpful, as well.  I’ve settled on both DATE and DATETIME, depending on whether 
the time is needed or not, which means I’ll have to change some of my code, but 
that will only strengthen the script in the long run.

Thanks again,
Frank
https://www.surfshopcart.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: DATETIME vs CHAR for "timestamp"

[shawn l.green]

On 4/14/2017 3:11 PM, SSC_perl wrote:

I have creation date/time fields in my script that are formatted as 
|MM|DD|hh|mm|ss.  Short of changing the script, should I set the field type 
in MySQL to DATETIME, or would it be better in terms of speed and efficiency to 
set it as char(19)?  Or would it not make a difference?

Thanks,
Frank




That all depends. Do you...

a) want mysqld to treat that column as an actual temporal value

or

b) want mysqld to see it as an opaque string of random alphanumeric 
characters


As you appear to have referred to this as a "creation date/time" 
tracking field it appears you want this to be treated like a temporal 
value so that you can easily do things like


SELECT ... WHERE create_date > NOW() - interval 7 days ;

If it's a temporal column, you can use functions like those in the next 
URL against it. If it's a string-type column, you can't unless you first 
convert your string into a temporal data type.


https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html


My suggestion is to use a native temporal data type (I recommend 
DATETIME) and that you review this section on how to format temporal 
literals (so that you can pass them easily from your application into 
MySQL)

https://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html

Using the correct data type is important to performance. You want to 
avoid forcing the server to perform too many implicit type conversions. 
Those usually nullify any performance improvements an index on those 
columns might provide:

https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

And the native DATETIME data type only needs 8 bytes to store its data 
while your CHAR(16) may need up to 64 bytes of storage.

https://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

DATETIME vs CHAR for "timestamp"

[SSC_perl]

I have creation date/time fields in my script that are formatted as 
|MM|DD|hh|mm|ss.  Short of changing the script, should I set the field type 
in MySQL to DATETIME, or would it be better in terms of speed and efficiency to 
set it as char(19)?  Or would it not make a difference?

Thanks,
Frank
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue with timestamp columns while working with MySQL load data in file

[hsv]

On 2015/04/12 08:52, Pothanaboyina Trimurthy wrote:

The problem is , as mentioned the load data is taking around 2 hours, I
have 2 timestamp columns for one column I am passing the input through load
data, and for the column DB_MODIFIED_DATETIME no input is provided, At
the end of the load data I could see only one timestamp value for both the
columns, though the load data takes 2 hours to load the data.

Can any one explain how exactly the load data infile works, and why only a
single timestamp is inserting for all 1 million records though the load
data taking around 2 hours.


Look up function SYSDATE: all other times  timestamps are kept in step, 
beginning at the time when the transaction begins. Your described effect 
is intended.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Issue with timestamp columns while working with MySQL load data in file

[Pothanaboyina Trimurthy]

Hi All,

I am facing an issue with timestamp columns while working with MySQL load
data in file, I am loading around a million records which is taking around
2 hours to complete the load data.

Before get into more details about the problem, first let me share the
table structure.

CREATE TABLE `test_load_data` (

  `id1` int(11) DEFAULT NULL,

  `col10` varchar(255) DEFAULT NULL,

  `DB_CREATED_DATETIME` datetime DEFAULT NULL,

  `DB_MODIFIED_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8

;

LOAD DATA LOCAL INFILE '/x.dat' INTO TABLE test_load_data

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\n'

(id1,col10,@DB_CREATED_DATETIME)

SET DB_CREATED_DATETIME = NOW()

;

mysql select DB_CREATED_DATETIME,DB_MODIFIED_DATETIME,count(1) from
test_load_data group by DB_CREATED_DATETIME,DB_MODIFIED_DATETIME;

+-+--+--+

| DB_CREATED_DATETIME | DB_MODIFIED_DATETIME | count(1) |

+-+--+--+

| 2015-04-07 10:08:09 | 2015-04-07 10:08:09  |  100 |

+-+--+--+

1 row in set (2.14 sec)

The problem is , as mentioned the load data is taking around 2 hours, I
have 2 timestamp columns for one column I am passing the input through load
data, and for the column DB_MODIFIED_DATETIME no input is provided, At
the end of the load data I could see only one timestamp value for both the
columns, though the load data takes 2 hours to load the data.

Can any one explain how exactly the load data infile works, and why only a
single timestamp is inserting for all 1 million records though the load
data taking around 2 hours.

Thank you in advance.

-- 
Thanks,
Trimurthy P
Mobile : +91 97397 64298
http://mysqlinternals.blogspot.in/
https://www.linkedin.com/pub/trimurthy-pothanaboyina/5a/9a9/96b

Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!

[Nick Cameo]

On Wed, Aug 21, 2013 at 10:39 PM, h...@tbbs.net wrote:

  2013/08/21 18:03 -0400, Nick Khamis 
 We have the following mysql timetampe field

 startdate | timestamp | NO   | | -00-00 00:00:00

 When trying to insert a long value in there:

 Calendar c = Calendar.getInstance(TimeZone.getTimeZone(UTC));
 c.getTimeInMillis();

 We are presented with the following error:

 com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect
 datetime value: '1377119243640' for column 'stopdate' at row 1
 
 Ugh, where is the SQL?

 In any case, although it looks as if that is MySQL s internal TIMESTAMP
 representation, one does not directly use Unix timestamps; instead, one
 converts them with the MySQL function FROM_UNIXTIME.

 The same effect may be gotten with any timestamp-formatting function that
 yields a string in the form '2013/08/21 18:03:00' (it is all one whether
 the separator is hyphen, slant, colon, ...).


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


What I am trying to accomplish is pass down a valid long value
(representative of UTC time)
that mysql timestamp field accepts, unix time epoch whatever That way,
I do not have to
fight with  java.sql.timestamp or java.sql.Date/Calander (for the
love!@E@#!@) for reads
and writes.

I can't use Joda until it has been included...

Deep breaths

What I tried is the following:

Straight Date:

update test set stopdate='2013-08-22T17:49:45'; - Works Fine

Formatted Date (long):

SimpleDateFormat sdf = new SimpleDateFormat(-MM-dd'T'HH:mm:ss, new
Locale(en, US));
long qu = sdf.parse(sdf.format(c.getTime())).getTime();

update test set stopdate='1377194323000'; - Zeros Out

Formatted Date (long with milliseconds):

c.getTimeInMillis()

update test set stopdate='1377195098956'; - Zeros Out

Formatted Date (long with milliseconds/1000):

c.getTimeInMillis() / 1000

update test set stopdate='1377195098.956';

Can't change the table field to bigint either, it's an already existing
project.

Someone please help before I fire myself :).

Kind Regards,

Nick.

Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!

[Nick Cameo]

Sorry, as was mentioned earlier:

select FROM_UNIXTIME(1377196112065/1000);
+---+
| FROM_UNIXTIME(1377196112065/1000) |
+---+
| 2013-08-22 18:28:32   |
+---+


Have a good day everyone :)

Nick.

Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!

[hsv]

 2013/08/22 14:22 -0400, Nick Cameo 
SimpleDateFormat sdf = new SimpleDateFormat(-MM-dd'T'HH:mm:ss, new 
Locale(en, US));

Well, you have your answer (FROM_UNIXTIME(   /1000)), but that stupid ISO 
format with 'T' in the middle does not work, because to MySQL letters are not 
separators--which, I am sorry to say, I did not say, although it was in the 
back of my mind.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!

[Nick Khamis]

Hello Everyone,

We have the following mysql timetampe field

startdate | timestamp | NO   | | -00-00 00:00:00

When trying to insert a long value in there:

Calendar c = Calendar.getInstance(TimeZone.getTimeZone(UTC));
c.getTimeInMillis();

We are presented with the following error:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect
datetime value: '1377119243640' for column 'stopdate' at row 1


Our environments is:

JDBC Driver = 5.1.26
Mysql = 5.5


show variables like 'time_zone%';
+---++
| Variable_name | Value  |
+---++
| time_zone | +00:00 |
+---++

SELECT @@global.sql_mode;
+---+
| @@global.sql_mode |
+---+
|   |
+---+

Not sure why I am getting this error.


Thanks in Advance,

Nick.

Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!

[Michael Dykman]

Nick,

You should have answered your own question in the text.

The MySql TIMESTAMP type is, as all other timestamps in the *nix
world, a count of seconds since epoch time.  The Java function you are
using yields MILLI-seconds.  Divide it by 1000 and you should be good
to go.


On Wed, Aug 21, 2013 at 6:03 PM, Nick Khamis sym...@gmail.com wrote:
 Hello Everyone,

 We have the following mysql timetampe field

 startdate | timestamp | NO   | | -00-00 00:00:00

 When trying to insert a long value in there:

 Calendar c = Calendar.getInstance(TimeZone.getTimeZone(UTC));
 c.getTimeInMillis();

 We are presented with the following error:

 com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect
 datetime value: '1377119243640' for column 'stopdate' at row 1


 Our environments is:

 JDBC Driver = 5.1.26
 Mysql = 5.5


 show variables like 'time_zone%';
 +---++
 | Variable_name | Value  |
 +---++
 | time_zone | +00:00 |
 +---++

 SELECT @@global.sql_mode;
 +---+
 | @@global.sql_mode |
 +---+
 |   |
 +---+

 Not sure why I am getting this error.


 Thanks in Advance,

 Nick.



-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!

[hsv]

 2013/08/21 18:03 -0400, Nick Khamis 
We have the following mysql timetampe field

startdate | timestamp | NO   | | -00-00 00:00:00

When trying to insert a long value in there:

Calendar c = Calendar.getInstance(TimeZone.getTimeZone(UTC));
c.getTimeInMillis();

We are presented with the following error:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect
datetime value: '1377119243640' for column 'stopdate' at row 1

Ugh, where is the SQL?

In any case, although it looks as if that is MySQL s internal TIMESTAMP 
representation, one does not directly use Unix timestamps; instead, one 
converts them with the MySQL function FROM_UNIXTIME.

The same effect may be gotten with any timestamp-formatting function that 
yields a string in the form '2013/08/21 18:03:00' (it is all one whether the 
separator is hyphen, slant, colon, ...).


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

string comparison for timestamp!

[Hal�sz S�ndor]

I was using this query and getting queer results:

SELECT MemberID, Surname, Houshold, Houmuch, CASE WHEN Houmuch  
(SELECT sharePrice FROM Stock) THEN ADDDATE(ereStart, (Houmuch * (SELECT hwyl 
FROM Stock)) / (SELECT regularPayment FROM Stock)) ELSE TIMESTAMPADD(YEAR, 2, 
CURDATE()) END AS goodThrough, ...
FROM EachName NATURAL JOIN (SELECT MemberID, SUM(Amount) AS Houmuch
FROM GiftandShare
WHERE Why = 2 AND Date = '2011/11/13'
GROUP BY MemberID) AS Paid NATURAL JOIN Address
HAVING goodThrough = '2011/11/13'
ORDER BY Surname

I looked at the date-comparisons and wondered how they were done: when I 
changed the slashes to hyphens it properly worked!

I think it would be better if MySQL had real timestamp-constants--and then the 
separator would matter: colon (and semicolon?) for the sexagesimal part and 
anything else for the day part.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Wrong conversion to timestamp from character string

[Hal�sz S�ndor]

 2011/08/18 18:30 +0300, Marius Feraru 
Thanks for your time reading my message, but I don't understand what
is the result context that you are talking about. Could you please
elaborate?

Well, if an expression is an argument to, say, CONCAT, the expression s 
result is character string. An argument to, say, POW is number. But the 
second and third arguments to IF have the same type, the type of the IF s 
context, and an expression that is an operand to SELECT may have any type: 
the result context does not require anything.

Now, your expression IF( DATE(d) = some-date, TIME(d), d ) is an operand to 
SELECT, and no type is required of it--but the types are not the same, 
wherefore there is at least one conversion, surely that the bare d is made 
character string. But it seems that instead TIME(d), a character string, is 
converted to some timestamp, a date. I wrote that I believe this an optimizer 
error because the least characterward tweak to this is enough to make it that 
which you seek: either concatenating empty string to TIME(d), thereby 
overriding any tendency the optimizer has to consider it other than a character 
string, or by concatenating empty string to the whole expression, thereby 
making the IF s result context character string, not any type.

Consider this:

-- 1) create test table containing a single DATETIME column
CREATE TABLE dt (d DATETIME);
-- 2) insert two test records
INSERT INTO dt VALUES (2011-08-07 10:11:12),(1234-05-06 07:08:09);

SELECT d, ADDTIME(IF( DATE(d)='2011-08-07', TIME(d), d), '1 1:1:1.02') as x 
FROM dt;

The outcome is really screwy:

+-++
| d   | x  |
+-++
| 2011-08-07 10:11:12 | 2035-12-13 02:00:00|
| 1234-05-06 07:08:09 | 1234-05-07 08:09:10.02 |
+-++

Surely there is a noteworthy bug here.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Timestamp value

[Jerry Schwartz]

When you UPDATE a record, a timestamp field (`t`) is set to the current time 
in the time zone given by @@time_zone, correct? That will usually be the local 
time.

If somebody in another time zone needs to compare `t` against //their own// 
local time, they need to use

CONVERT_TZ(`t`,'my_local_time zone','their_local_time_zone`)

Am I right?

So, what if they do not know my local time zone? Is there a way to create a 
timestamp field that is always in UTC? I don't think there is, without using a 
trigger.

Am I right about that?



Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Timestamp value

[Johan De Meersman]

I may be mistaken, but isn't UTC pretty much GMT if you don't want subsecond 
precision? Set your server's timezone to GMT and you should get what you want.

- Original Message -
 From: Jerry Schwartz je...@gii.co.jp
 To: mysql@lists.mysql.com
 Sent: Monday, 6 June, 2011 5:10:22 PM
 Subject: Timestamp value
 
 When you UPDATE a record, a timestamp field (`t`) is set to the
 current time
 in the time zone given by @@time_zone, correct? That will usually be
 the local
 time.
 
 If somebody in another time zone needs to compare `t` against //their
 own//
 local time, they need to use
 
 CONVERT_TZ(`t`,'my_local_time zone','their_local_time_zone`)
 
 Am I right?
 
 So, what if they do not know my local time zone? Is there a way to
 create a
 timestamp field that is always in UTC? I don't think there is,
 without using a
 trigger.
 
 Am I right about that?
 
 
 
 Regards,
 
 Jerry Schwartz
 Global Information Incorporated
 195 Farmington Ave.
 Farmington, CT 06032
 
 860.674.8796 / FAX: 860.674.8341
 E-mail: je...@gii.co.jp
 Web site: www.the-infoshop.com
 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Timestamp value

[Jerry Schwartz]

-Original Message-
From: Johan De Meersman [mailto:vegiv...@tuxera.be]
Sent: Monday, June 06, 2011 12:57 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Timestamp value


I may be mistaken, but isn't UTC pretty much GMT if you don't want subsecond
precision? Set your server's timezone to GMT and you should get what you 
want.

[JS] I don't want to affect everything on the server (scheduled events, file 
modification dates, etc.) I only want one UTC (or GMT, I don't really care) 
field.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

First and Last timestamp of the day/week / month

[Cantwell, Bryan]

I need to be able to get a first and last timestamp for a day a week or a
month. I have an example of what I did so far that gets me that info for a
week... but I fear that it is far more complex than it needs to be. Anyone
have a simple way to get first and last timestamp for these intervals?

SELECT
  timestamp(ADDDATE(CURDATE(), -1 * (DAYOFWEEK(CURDATE()) - 1)))
  , from_unixtime(unix_timestamp(ADDDATE(CURDATE()+6, -1 *
(DAYOFWEEK(CURDATE()) - 1)))+86399)

Re: First and Last timestamp of the day/week / month

[Alister West]

You could rewrite it english friendly

(5.1.37)

SET @DAY_START = curdate();
SET @WEEK_START = curdate()  - interval weekday(now()) DAY;
SET @MONTH_START = date_format(curdate(), %Y-%m-01);

## DAY
SELECT timestamp(@DAY_START) as min_ts,
 timestamp(@DAY_START + INTERVAL 1 DAY
  - INTERVAL 1 SECOND
 ) as max_ts ;

## WEEK
SELECT timestamp(@WEEK_START) as min_ts,
 timestamp(@WEEK_START + INTERVAL 1 WEEK
   - INTERVAL 1 SECOND
) as max_ts ;

## MONTH
SELECT timestamp(@MONTH_START) as min_ts,
 timestamp(@MONTH_START + INTERVAL 1 MONTH
- INTERVAL 1 SECOND
) as max_ts  ;




~~
 c|_|  Alister West - Saving the world from coffee!




On 22 April 2010 14:25, Cantwell, Bryan bcantw...@firescope.com wrote:
 I need to be able to get a first and last timestamp for a day a week or a
 month. I have an example of what I did so far that gets me that info for a
 week... but I fear that it is far more complex than it needs to be. Anyone
 have a simple way to get first and last timestamp for these intervals?

 SELECT
  timestamp(ADDDATE(CURDATE(), -1 * (DAYOFWEEK(CURDATE()) - 1)))
  , from_unixtime(unix_timestamp(ADDDATE(CURDATE()+6, -1 *
 (DAYOFWEEK(CURDATE()) - 1)))+86399)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Creating subsets on timestamp with modulo, date_trunc and ?suggestions?

[Davor J.]

A simple way to do this is to truncate the date and then GROUP BY it. So if 
you have 2009-08-08, and you want a subset on month, then just truncate the 
day-part: 2009-08-00 on the whole column, and SELECT DISTINCT so you have a 
subset. You can use this subset then to join the dates, GROUP BY and 
aggregate

An other way I found is described in Celko's 'SQL for smarties'. He uses 
modulo there. It seems powerful, but also tricky to implement for dates.

I was wondering if anyone knew some other way to create a subset of a 
timestamp column. Any input is welcome.

Regards,
Davor



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Help with Timestamp invalid value error

[Proemial]

I run a process which loads a series of timestamped data into a table.
 I  use the TIMESTAMP column.

I have a single value with a timestamp of '2008-03-9 2:56:34.737'
which fails on insert with 'incorrect datetime'.  Days before, and
after work.  Hours later in the day work.  I thought it might be
related to the timezone, but have none of the TZ tables populated.

Can someone tell me why this date in particular fails?

The query that fails:
INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID,
topic_list, product_list)
VALUES (
'2008-03-9 2:56:34.737',
'',
'',
'')

Table:
newsID  int(10) unsignedPRI auto_increment
timeStamp   timestamp   CURRENT_TIMESTAMP   on update 
CURRENT_TIMESTAMP
TRStoryID   varchar(128)
topic_list  varchar(512)
product_listvarchar(512)

Thank you!
Martin
-- 
---
This is a signature.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Help with Timestamp invalid value error

[John Daisley]

What is your sql_mode set to?

I tried inserting that value into a timestamp column on our test server and it 
works fine.

Regards

John Daisley
Mobile +44(0)7812 451238
Email j...@butterflysystems.co.uk

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer
Cognos BI Developer

---
Sent from HP IPAQ mobile device.



-Original Message-
From: Proemial proem...@gmail.com
Sent: Friday, September 04, 2009 2:27 PM
To: mysql@lists.mysql.com
Subject: Help with Timestamp invalid value error

I run a process which loads a series of timestamped data into a table.
 I  use the TIMESTAMP column.

I have a single value with a timestamp of '2008-03-9 2:56:34.737'
which fails on insert with 'incorrect datetime'.  Days before, and
after work.  Hours later in the day work.  I thought it might be
related to the timezone, but have none of the TZ tables populated.

Can someone tell me why this date in particular fails?

The query that fails:
INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID,
topic_list, product_list)
VALUES (
'2008-03-9 2:56:34.737',
'',
'',
'')

Table:
newsID  int(10) unsignedPRI auto_increment
timeStamp   timestamp   CURRENT_TIMESTAMP   on update 
CURRENT_TIMESTAMP
TRStoryID   varchar(128)
topic_list  varchar(512)
product_listvarchar(512)



[The entire original message is not included]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Fwd: Help with Timestamp invalid value error

[Proemial]

Currently set to:
NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

On Fri, Sep 4, 2009 at 10:24 AM, John
Daisleyj...@butterflysystems.co.uk wrote:
 What is your sql_mode set to?

 I tried inserting that value into a timestamp column on our test server and 
 it works fine.

 Regards

 John Daisley
 Mobile +44(0)7812 451238
 Email j...@butterflysystems.co.uk

 Certified MySQL 5 Database Administrator (CMDBA)
 Certified MySQL 5 Developer
 Cognos BI Developer

 ---
 Sent from HP IPAQ mobile device.



 -Original Message-
 From: Proemial proem...@gmail.com
 Sent: Friday, September 04, 2009 2:27 PM
 To: mysql@lists.mysql.com
 Subject: Help with Timestamp invalid value error

 I run a process which loads a series of timestamped data into a table.
  I  use the TIMESTAMP column.

 I have a single value with a timestamp of '2008-03-9 2:56:34.737'
 which fails on insert with 'incorrect datetime'.  Days before, and
 after work.  Hours later in the day work.  I thought it might be
 related to the timezone, but have none of the TZ tables populated.

 Can someone tell me why this date in particular fails?

 The query that fails:
 INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID,
 topic_list, product_list)
 VALUES (
 '2008-03-9 2:56:34.737',
 '',
 '',
 '')

 Table:
 newsID  int(10) unsigned        PRI             auto_increment
 timeStamp       timestamp       CURRENT_TIMESTAMP       on update 
 CURRENT_TIMESTAMP
 TRStoryID       varchar(128)
 topic_list      varchar(512)
 product_list    varchar(512)



 [The entire original message is not included]





-- 
---
This is a signature.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Fwd: Help with Timestamp invalid value error

[John Daisley]

Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'may' be 
causing the problem.

Try inserting the value as

 '2008-03-09 02:56:34.737'

Do you get the same error?

What mysql version is your server?



Regards

John Daisley
Mobile +44(0)7812 451238
Email j...@butterflysystems.co.uk

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer
Cognos BI Developer

---
Sent from HP IPAQ mobile device.



-Original Message-
From: Proemial proem...@gmail.com
Sent: Friday, September 04, 2009 3:39 PM
To: mysql@lists.mysql.com
Subject: Fwd: Help with Timestamp invalid value error

Currently set to:
NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

On Fri, Sep 4, 2009 at 10:24 AM, John
Daisleyj...@butterflysystems.co.uk wrote:
 What is your sql_mode set to?

 I tried inserting that value into a timestamp column on our test server and 
 it works fine.

 Regards

 John Daisley
 Mobile +44(0)7812 451238
 Email j...@butterflysystems.co.uk

 Certified MySQL 5 Database Administrator (CMDBA)
 Certified MySQL 5 Developer
 Cognos BI Developer

 ---
 Sent from HP IPAQ mobile device.





[The entire original message is not included]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Fwd: Help with Timestamp invalid value error

[Proemial]

I had already tried that, actually.  Produces the same error.  I
should have mentioned that as well, sorry!

The version is 5.1.34

thanks for the help, btw!
Martin

On Fri, Sep 4, 2009 at 11:14 AM, John
Daisleyj...@butterflysystems.co.uk wrote:
 Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'may' 
 be causing the problem.

 Try inserting the value as

  '2008-03-09 02:56:34.737'

 Do you get the same error?

 What mysql version is your server?



 Regards

 John Daisley
 Mobile +44(0)7812 451238
 Email j...@butterflysystems.co.uk

 Certified MySQL 5 Database Administrator (CMDBA)
 Certified MySQL 5 Developer
 Cognos BI Developer

 ---
 Sent from HP IPAQ mobile device.



 -Original Message-
 From: Proemial proem...@gmail.com
 Sent: Friday, September 04, 2009 3:39 PM
 To: mysql@lists.mysql.com
 Subject: Fwd: Help with Timestamp invalid value error

 Currently set to:
 NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 On Fri, Sep 4, 2009 at 10:24 AM, John
 Daisleyj...@butterflysystems.co.uk wrote:
 What is your sql_mode set to?

 I tried inserting that value into a timestamp column on our test server and 
 it works fine.

 Regards

 John Daisley
 Mobile +44(0)7812 451238
 Email j...@butterflysystems.co.uk

 Certified MySQL 5 Database Administrator (CMDBA)
 Certified MySQL 5 Developer
 Cognos BI Developer

 ---
 Sent from HP IPAQ mobile device.





 [The entire original message is not included]




-- 
---
This is a signature.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Fwd: Help with Timestamp invalid value error

[Gavin Towey]

Mysql doesn't store sub-second values.

try
2008-03-09 02:56:34
Instead of
2008-03-09 02:56:34.737

Regards,
Gavin Towey

-Original Message-
From: Proemial [mailto:proem...@gmail.com]
Sent: Friday, September 04, 2009 8:37 AM
To: John Daisley
Cc: mysql@lists.mysql.com
Subject: Re: Fwd: Help with Timestamp invalid value error

I had already tried that, actually.  Produces the same error.  I
should have mentioned that as well, sorry!

The version is 5.1.34

thanks for the help, btw!
Martin

On Fri, Sep 4, 2009 at 11:14 AM, John
Daisleyj...@butterflysystems.co.uk wrote:
 Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'may' 
 be causing the problem.

 Try inserting the value as

  '2008-03-09 02:56:34.737'

 Do you get the same error?

 What mysql version is your server?



 Regards

 John Daisley
 Mobile +44(0)7812 451238
 Email j...@butterflysystems.co.uk

 Certified MySQL 5 Database Administrator (CMDBA)
 Certified MySQL 5 Developer
 Cognos BI Developer

 ---
 Sent from HP IPAQ mobile device.



 -Original Message-
 From: Proemial proem...@gmail.com
 Sent: Friday, September 04, 2009 3:39 PM
 To: mysql@lists.mysql.com
 Subject: Fwd: Help with Timestamp invalid value error

 Currently set to:
 NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 On Fri, Sep 4, 2009 at 10:24 AM, John
 Daisleyj...@butterflysystems.co.uk wrote:
 What is your sql_mode set to?

 I tried inserting that value into a timestamp column on our test server and 
 it works fine.

 Regards

 John Daisley
 Mobile +44(0)7812 451238
 Email j...@butterflysystems.co.uk

 Certified MySQL 5 Database Administrator (CMDBA)
 Certified MySQL 5 Developer
 Cognos BI Developer

 ---
 Sent from HP IPAQ mobile device.





 [The entire original message is not included]




--
---
This is a signature.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

how to get the timestamp from remote mysql

[Nathan Huang]

Hi guys
I am going to fetch out the data from remote mysql database according to 
timestamps colmmen, however my local date is different from the one on 
remote mysql database,
how can I get right timestamp using the date of remote time zone? that 
is to say I set the date and send itto remote server or database to 
calculate  timestamps of it

thanks in advance
nathan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

R: how to get the timestamp from remote mysql

[Claudio Nanni]

When you are using SQL connected to the server (mysql -ublah)  the timestamp
is the server one. If you use app time functions it will be client time.
Bottom line: use server sql functions.
Ciao
Claudio

Il giorno 4 lug, 2009 8:50 m., Nathan Huang nathan.vorbei.t...@gmail.com
ha scritto:

Hi guys
I am going to fetch out the data from remote mysql database according to
timestamps colmmen, however my local date is different from the one on
remote mysql database,
how can I get right timestamp using the date of remote time zone? that is to
say I set the date and send itto remote server or database to calculate
 timestamps of it
thanks in advance
nathan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com

Re: how to get the timestamp from remote mysql

[Marcus Bointon]

On 4 Jul 2009, at 07:48, Nathan Huang wrote:

I am going to fetch out the data from remote mysql database  
according to timestamps colmmen, however my local date is different  
from the one on remote mysql database,
how can I get right timestamp using the date of remote time zone?  
that is to say I set the date and send itto remote server or  
database to calculate  timestamps of it

thanks in advance



There is one straightforward solution: only ever keep timestamps in  
UTC/GMT and correct for time zone / DST when you display them. While  
that can be confusing sometimes, overall it's a much simpler way of  
working that storing local times.


Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of i...@hand CRM solutions
mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: how to get the timestamp from remote mysql

[Michael Dykman]

Assuming that synchornizing the clocks between the systems is beyond
your control you could try getting the UTC timestamp from both
systems, then adjust your date/time math according to the difference
between them.

   select unix_timestamp(utc_timestamp());

It is more than a little hacky, but it will work

 - michael dykman


On Sat, Jul 4, 2009 at 2:48 AM, Nathan
Huangnathan.vorbei.t...@gmail.com wrote:
 Hi guys
 I am going to fetch out the data from remote mysql database according to
 timestamps colmmen, however my local date is different from the one on
 remote mysql database,
 how can I get right timestamp using the date of remote time zone? that is to
 say I set the date and send itto remote server or database to calculate
  timestamps of it
 thanks in advance
 nathan

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com





-- 
 - michael dykman
 - mdyk...@gmail.com

 - All models are wrong.  Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Need help to query with timestamp in C++

[Kandy Wong]

Hi Saul,

I need to use C++ and I'm not writing a web application.
Thanks anyway.

Kandy

 I have done queries to the database in PHP with variables like month but
 easily can select from a range of time and data to produce the same
 results, the output goes directly to the web so if that is what you are
 seeking for, I can help with PHP.

 Saul

 Kandy Wong wrote:
 Hi,

 I need to write a C++ program in a Linux environment to query with a
 timestamp.
 The user will only provide with an approximate time so I'd like to know
 how can I write a program or a query to return the closest data.

 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest data?
 I
 know I can make use of 'LIKE' but this will return more than one data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
Kandy Wong
Scientific Programmer Analyst
TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics)
4004 Wesbrook Mall
Vancouver, BC, Canada, V6T 2A3
Phone: (604) 222- 1047 ext. 6193
Email: [EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help to query with timestamp in C++

[Dušan Pavlica]

Hi Kandy,

this could be the query you are looking for. It should return record 
with the closest timestamp to your required time:


(SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM 
table1 t

WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column DESC LIMIT 1
)
UNION
(SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM 
table1 t

WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column LIMIT 1
)
ORDER BY diff LIMIT 1

HTH,
Dusan

Kandy Wong napsal(a):

Hi,

I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.

The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |

For example, '2008-08-05 04:01:39' is the time provided by the user which
does not exist in the database.  So, how can I return the closest data?  I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.

Kandy


  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help to query with timestamp in C++

[walter harms]

Kandy Wong wrote:
 Hi Saul,
 
 I need to use C++ and I'm not writing a web application.
 Thanks anyway.



you can do something like:

select min(abs(timediff(targettime,timestamp))) from table where 
condition ;

if you use the libmysql you can get the result as strings back (the method i 
prefer) and convert them
in what ever you need.

re,
 wh




 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest data?
 I
 know I can make use of 'LIKE' but this will return more than one data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


 
 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help to query with timestamp in C++

[Warren Young]

Kandy Wong wrote:


And what is the good connector (C++ to MySQL) to use?


MySQL++ (http://tangentsoft.net/mysql++/) has native Date, Time, and 
DateTime data types.  You can convert to these types implicitly:


mysqlpp::DateTime dt = row[my_column];

Row::operator[] doesn't return DateTime, it returns a stringish type, 
which can convert itself to lots of different C++ data types.  This is 
useful because the MySQL C API normally returns results in string form, 
so you need a natural way to convert these values to the native C++ 
types for processing.  In this particular case, it saves you from having 
to do the timestamp string parsing yourself.

--
Warren Young, maintainer of MySQL++

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Need help to query with timestamp in C++

[Jerry Schwartz]

I'm puzzled by the layout of your table, if that's what you're showing us. Is 
the timestamp in the table truly associated with the time at which the user 
put in his approximate time?

If, for example, the user types in 04:05:07 at 04:03:02, and then types in 
04:02:59 at 04:03:03, what is it that you want to return?

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Kandy Wong [mailto:[EMAIL PROTECTED]
Sent: Friday, August 15, 2008 2:36 AM
To: Saul Bejarano
Cc: mysql@lists.mysql.com
Subject: Re: Need help to query with timestamp in C++

Hi Saul,

I need to use C++ and I'm not writing a web application.
Thanks anyway.

Kandy

 I have done queries to the database in PHP with variables like month
but
 easily can select from a range of time and data to produce the same
 results, the output goes directly to the web so if that is what you
are
 seeking for, I can help with PHP.

 Saul

 Kandy Wong wrote:
 Hi,

 I need to write a C++ program in a Linux environment to query with a
 timestamp.
 The user will only provide with an approximate time so I'd like to
know
 how can I write a program or a query to return the closest data.

 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest
data?
 I
 know I can make use of 'LIKE' but this will return more than one
data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
Kandy Wong
Scientific Programmer Analyst
TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics)
4004 Wesbrook Mall
Vancouver, BC, Canada, V6T 2A3
Phone: (604) 222- 1047 ext. 6193
Email: [EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help to query with timestamp in C++

[Kandy Wong]

Hi Dusan,

Thank you so much. It works!

Kandy

 Hi Kandy,

 this could be the query you are looking for. It should return record
 with the closest timestamp to your required time:

 (SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM
 table1 t
  WHERE timestamp_column = '20080815091907'
  ORDER BY timestamp_column DESC LIMIT 1
 )
 UNION
 (SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM
 table1 t
  WHERE timestamp_column = '20080815091907'
  ORDER BY timestamp_column LIMIT 1
 )
 ORDER BY diff LIMIT 1

 HTH,
 Dusan

 Kandy Wong napsal(a):
 Hi,

 I need to write a C++ program in a Linux environment to query with a
 timestamp.
 The user will only provide with an approximate time so I'd like to know
 how can I write a program or a query to return the closest data.

 The followings are the timestamp in the MySQL database:
 | 2008-08-05 03:56:09 | 1217933769 |
 | 2008-08-05 03:56:19 | 1217933779 |
 | 2008-08-05 03:56:29 | 1217933789 |
 | 2008-08-05 03:59:39 | 1217933979 |
 | 2008-08-05 03:59:49 | 1217933989 |
 | 2008-08-05 03:59:59 | 1217933999 |
 | 2008-08-05 04:02:39 | 1217934159 |
 | 2008-08-05 04:02:49 | 1217934169 |
 | 2008-08-05 04:02:59 | 1217934179 |

 For example, '2008-08-05 04:01:39' is the time provided by the user
 which
 does not exist in the database.  So, how can I return the closest data?
 I
 know I can make use of 'LIKE' but this will return more than one data.
 What is the best method to get the closest one?
 And what is the good connector (C++ to MySQL) to use?
 Any suggestion?
 Thank you.

 Kandy


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need help to query with timestamp in C++

[Kandy Wong]

Hi,

I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.

The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |

For example, '2008-08-05 04:01:39' is the time provided by the user which
does not exist in the database.  So, how can I return the closest data?  I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.

Kandy


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help to query with timestamp in C++

[Saul Bejarano]

I have done queries to the database in PHP with variables like month but 
easily can select from a range of time and data to produce the same 
results, the output goes directly to the web so if that is what you are 
seeking for, I can help with PHP.


Saul

Kandy Wong wrote:

Hi,

I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.

The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |

For example, '2008-08-05 04:01:39' is the time provided by the user which
does not exist in the database.  So, how can I return the closest data?  I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.

Kandy





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Timestamp and the On Update Current_Timestamp clause

[Moon's Father]

Here is my test.
Any way can retrieve the metadata.

On Fri, May 9, 2008 at 10:45 PM, Martijn Tonies [EMAIL PROTECTED]
wrote:



  SHOW CREATE TABLE ...

 Yes, I thought so :-(


 From a coding point of view, this requires parsing...

 Why isn't there anything in show full columns.


 Martijn Tonies
 Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle 
 MS SQL Server
 Upscene Productions
 http://www.upscene.com
 My thoughts:
 http://blog.upscene.com/martijn/
 Database development questions? Check the forum!
 http://www.databasedevelopmentforum.com


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]




-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Timestamp and the On Update Current_Timestamp clause

[Martijn Tonies]

Hi,

How does one know if ON UPDATE CURRENT_TIMESTAMP was specified when
creating a column? How do I retrieve this bit of info from the metadata
queries?
(also MySQL 4.1)

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle 
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Timestamp and the On Update Current_Timestamp clause

[Ben Clewett]

SHOW CREATE TABLE ...

Martijn Tonies wrote:

Hi,

How does one know if ON UPDATE CURRENT_TIMESTAMP was specified when
creating a column? How do I retrieve this bit of info from the metadata
queries?
(also MySQL 4.1)

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle 
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Timestamp and the On Update Current_Timestamp clause

[Martijn Tonies]

 SHOW CREATE TABLE ...

Yes, I thought so :-(


From a coding point of view, this requires parsing...

Why isn't there anything in show full columns.


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle 
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problems with timestamp and leap seconds?

[Tim McDaniel]

Howdy -- new to the list.

BigCorp has a Bugzilla database that uses version 4.1.7-standard.
We've been taking backups using mysqldump.  I thought to verify a
backup, in essence by
mysqldump bugzilla  B
mysql test  B
mysqldump test  T
diff B T

Everything is the same, except that the timestamps I've looked at are
all 22 or 23 seconds earlier in the test database than in the original
database.  E.g., from a line-by-line diff of the mysqldump output:
line 697609, characters 15 on:
...sions VALUES ('val1','PROD1','2005-09-14 15:21:03');
...sions VALUES ('val1','PROD1','2005-09-14 15:21:41');
Specifically, the discrepancy appears to be the number of leap seconds
that were in effect at the point of the timestamp.  E.g.,
line 697611, characters 15 on:
...sions VALUES ('val2','PROD2','2006-07-31 11:30:57');
...sions VALUES ('val2','PROD2','2006-07-31 11:30:34');
Since the timestamps are from 2003 on, and
http://en.wikipedia.org/wiki/Leap_seconds says that there's been
only one leap second added (at the end of 2005), and I've looked at
only a few dozen lines of diff, I can't tell for sure that that's it,
but it seems quite likely.

It's not an artifact of mysqldump in particular: select shows the same
thing.

I've tried Googling and man mysqldump, but all I can find is
information on setting up timezone tables in MySQL.

In case it matters, SELECT COUNT(*) FROM mysql.time_zone_name;
returns 0, and
$ mysqladmin variables | grep zone
| system_time_zone| CST   |
| time_zone   | SYSTEM|

Is there some other configuration information I need to provide?
Any hints?

--
Tim McDaniel, [EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problems with timestamp and leap seconds?

[Tim McDaniel]

I had a bit of BFOTO and tried simple inserts.

mysql  create table t (f timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql insert into t values ('2008-03-04 16:17:00');
Query OK, 1 row affected (0.00 sec)

mysql select * from t;
+-+
| f   |
+-+
| 2008-03-04 16:17:37 |
+-+
1 row in set (0.00 sec)

How very, very peculiar.  Furthermore, the difference is 37 seconds,
but I don't see the significance of that particular value.

More data: Red Hat Linux 9 (as ancient as the Bugzilla).  Kernel
2.4.20.  I put new timezone info on the system in March of last year,
just before the new US Daylight Saving Time rules went into effect.
(I had wondered whether perhaps there hadn't been leap second info
before then, so timestamps stored in an internal format would have
jumped at the transition, but this example above doesn't have any sort
of transition involved that I can see.)

--
Tim McDaniel, [EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Problems with timestamp and leap seconds?

[Jay Blanchard]

[snip]
I had a bit of BFOTO and tried simple inserts.

 mysql  create table t (f timestamp);
 Query OK, 0 rows affected (0.00 sec)

 mysql insert into t values ('2008-03-04 16:17:00');
 Query OK, 1 row affected (0.00 sec)

 mysql select * from t;
 +-+
 | f   |
 +-+
 | 2008-03-04 16:17:37 |
 +-+
 1 row in set (0.00 sec)
[/snip]

The column type needs to be DATETIME. 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Problems with timestamp and leap seconds?

[Tim McDaniel]

On Tue, 4 Mar 2008, Jay Blanchard [EMAIL PROTECTED] wrote:

[snip]
I had a bit of BFOTO and tried simple inserts.

mysql  create table t (f timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql insert into t values ('2008-03-04 16:17:00');
Query OK, 1 row affected (0.00 sec)

mysql select * from t;
+-+
| f   |
+-+
| 2008-03-04 16:17:37 |
+-+
1 row in set (0.00 sec)
[/snip]

The column type needs to be DATETIME.


Thank you for pointing me at TIMESTAMP versus DATETIME.  I'll read
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html
thoroughly when I can.

Can you give a little more detail as to why DATETIME is necessary?
Glancing briefly at
http://dev.mysql.com/doc/refman/4.1/en/timestamp.html, I see
(emphasis added):

Beginning with MySQL 4.1.3, the default current time zone for each
connection is the server's time. The time zone can be set on a
per-connection basis, as described in Section 9. but are converted
from the current time zone for storage, and converted back to the
current time zone for retrieval. As long as the time zone
setting remains constant, you get back the same value you
store. If you store a TIMESTAMP value, and then change the time
zone and retrieve the value, the retrieved value is different from
the value you stored. This occurs because the same time zone was
not used for conversion in both directions. The current time zone
is available as the value of the time_zone system variable.

In any event, the database has table definitions with columns like
`creation_ts` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
Also, it's a Bugzilla database, and I'm not free to change the
schema.

--
Tim McDaniel, [EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Problems with timestamp and leap seconds?

[Jay Blanchard]

[snip]
 The column type needs to be DATETIME.

Thank you for pointing me at TIMESTAMP versus DATETIME.  I'll read
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html
thoroughly when I can.

Can you give a little more detail as to why DATETIME is necessary?
[/snip]

It was much too quick a reply on my part but it is my understanding that
a TIMESTAMP field is updated according to server time and you cannot
actually insert a value. I may be wrong as I have never tested this.

On the other hand a DATETIME field accepts inserts.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Problems with timestamp and leap seconds?

[Tim McDaniel]

On Tue, 4 Mar 2008, Jay Blanchard [EMAIL PROTECTED] wrote:

It was much too quick a reply on my part but it is my understanding
that a TIMESTAMP field is updated according to server time and you
cannot actually insert a value. I may be wrong as I have never
tested this.


Even in pre-4.1 versions, per
http://dev.mysql.com/doc/refman/4.1/en/timestamp-pre-4-1.html,
You can set any TIMESTAMP column to a value different from the
current date and time by setting it explicitly to the desired
value. This is true even for the first TIMESTAMP column.
though in those versions it took some work to keep it from updating
automatically.  Those limitations have apparently been lifted starting
from 4.1.

Experimentally,

$ mysql ...
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 122433 to server version: 4.1.7-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql create table u (f timestamp NOT NULL default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql insert into u values ('2007-02-03 7:08:09');
Query OK, 1 row affected (0.00 sec)

mysql select * from u;
+-+
| f   |
+-+
| 2007-02-03 07:08:46 |
+-+
1 row in set (0.00 sec)

TIMESTAMP columns *can* be used to update according to server time,
as explained at
http://dev.mysql.com/doc/refman/4.1/en/timestamp.html, and as shown
above with
default CURRENT_TIMESTAMP
-- but that does it when you set it to NULL via INSERT or UPDATE
on update CURRENT_TIMESTAMP
-- but that does it when you update some other column
   without setting this TIMESTAMP column
or, of course, setting it to NOW() or one of its synonyms.

--
Tim McDaniel, [EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

repost timestamp for update insert

[Hiep Nguyen]

i asked this question before, but when i tried what D.Vin suggested, i got 
an error.


mysql version is 4.1.12

i tried:

CREATE TABLE tbl_spdate(
spdate_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT ,
spdate_date date NOT NULL ,
spdate_notes varchar( 100 ) ,
spdate_created timestamp default 0,
spdate_updated default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

and i got this:

#1064 - 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 
'default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

)' at line 7

am i have a wrong version?

Thanks,
T. Hiep

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: repost timestamp for update insert

[joe]

Your missing your data type for the spdate_updated attribute

CREATE TABLE tbl_spdate(
spdate_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT , spdate_date date NOT
NULL , spdate_notes varchar( 100 ) , spdate_created timestamp default 0,
spdate_updated timestamp default CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP ); 

-Original Message-
From: Hiep Nguyen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 29, 2007 8:24 AM
To: mysql@lists.mysql.com
Subject: repost timestamp for update  insert

i asked this question before, but when i tried what D.Vin suggested, i got
an error.

mysql version is 4.1.12

i tried:

CREATE TABLE tbl_spdate(
spdate_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT , spdate_date date NOT
NULL , spdate_notes varchar( 100 ) , spdate_created timestamp default 0,
spdate_updated default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

and i got this:

#1064 - 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
'default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )' at line 7

am i have a wrong version?

Thanks,
T. Hiep

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00
AM
 

No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00
AM
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: repost timestamp for update insert

[Jerry Schwartz]

I think you are missing the data type for spdate_updated.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com

 -Original Message-
 From: Hiep Nguyen [mailto:[EMAIL PROTECTED]
 Sent: Thursday, November 29, 2007 10:24 AM
 To: mysql@lists.mysql.com
 Subject: repost timestamp for update  insert

 i asked this question before, but when i tried what D.Vin suggested, i
 got
 an error.

 mysql version is 4.1.12

 i tried:

 CREATE TABLE tbl_spdate(
 spdate_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT ,
 spdate_date date NOT NULL ,
 spdate_notes varchar( 100 ) ,
 spdate_created timestamp default 0,
 spdate_updated default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 );

 and i got this:

 #1064 - 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
 'default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 )' at line 7

 am i have a wrong version?

 Thanks,
 T. Hiep

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 infoshop.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problem with mysqlimport and timestamp

[qt4x11]

Hi-
I'm using the command 'mysqlimport -u usr -ppassh -h mysqlserver -P 3306 -v
db --local $workdir/$filename'to import a table into mysql from a file
$filename.

The data in $filename looks something like:

test test

where there is a blank space between the two 'test's to represent an empty
column. This column is of type datetime NULL DEFAULT NULL in the database.
The blank space between the two 'test's gets imported as (err) instead of
NULL.


Is there a way I can import empty column data as NULL? I'd like there to be
a NULL for every row for which this column is empty.  I'm not sure how to do
this given the format of my $filename.  As a check, I tried inserting a row
at the top of $filename like

test

The row was imported correctly, as in there was a NULL in the timestamp
column in the database, as well as a NULL in the next column in the
database.  So, this may have something to do with the format of the
$filename, but I may not be able to control the format of that file.

Thanks.

Re: Problem with mysqlimport and timestamp

[mysql]

qt4x11 wrote:

Hi-
I'm using the command 'mysqlimport -u usr -ppassh -h mysqlserver -P 3306 -v
db --local $workdir/$filename'to import a table into mysql from a file
$filename.

The data in $filename looks something like:

test test

where there is a blank space between the two 'test's to represent an empty
column. This column is of type datetime NULL DEFAULT NULL in the database.
The blank space between the two 'test's gets imported as (err) instead of
NULL.


Is there a way I can import empty column data as NULL? I'd like there to be
a NULL for every row for which this column is empty.  I'm not sure how to do
this given the format of my $filename.  As a check, I tried inserting a row
at the top of $filename like

test

The row was imported correctly, as in there was a NULL in the timestamp
column in the database, as well as a NULL in the next column in the
database.  So, this may have something to do with the format of the
$filename, but I may not be able to control the format of that file.

Thanks.



You should alter your data like so:

test\N  test

That is, separate the fields with a tab and place \N wherever you want 
a NULL. As it is, the data is being misinterpreted, which is why the 
second import you mentioned worked--there was nothing after the first 
field to be erroneously put into the timestamp field.


If the data comes from somewhere else you will need to parse it out and 
re-write it using proper formatting, i'm afraid.


brian

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

timestamp for update and insert

[Hiep Nguyen]

Hi list,

i tried to create a table with inserted  lastupdated timestamp fields:

create table temp (
id int not null primary ke auto_increment,
data varchar(100),
inserted timestamp default now(),
lastupdated timestamp(8));


how do i get mysql to put in the current timestamp for inserted  
lastupdated fields when i insert a record and only lastupdated when i 
update the record?


thanks,
T. Hiep


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timestamp for update and insert

[Olaf Stein]

I would use a trigger (at least for the update)

The first insert should work with now() and you can leave lastupdateted
empty


Olaf


On 9/4/07 3:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:

 Hi list,
 
 i tried to create a table with inserted  lastupdated timestamp fields:
 
 create table temp (
 id int not null primary ke auto_increment,
 data varchar(100),
 inserted timestamp default now(),
 lastupdated timestamp(8));
 
 
 how do i get mysql to put in the current timestamp for inserted 
 lastupdated fields when i insert a record and only lastupdated when i
 update the record?
 
 thanks,
 T. Hiep
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timestamp for update and insert

[Michael Dykman]

Triggers are a fine idea, but I would use a trigger for both cases..
no point putting that level of housekeeping on the application when
you can set rules in the database and more or less forget about it.

 - michael


On 9/4/07, Olaf Stein [EMAIL PROTECTED] wrote:
 I would use a trigger (at least for the update)

 The first insert should work with now() and you can leave lastupdateted
 empty


 Olaf


 On 9/4/07 3:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:

  Hi list,
 
  i tried to create a table with inserted  lastupdated timestamp fields:
 
  create table temp (
  id int not null primary ke auto_increment,
  data varchar(100),
  inserted timestamp default now(),
  lastupdated timestamp(8));
 
 
  how do i get mysql to put in the current timestamp for inserted 
  lastupdated fields when i insert a record and only lastupdated when i
  update the record?
 
  thanks,
  T. Hiep
 


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timestamp for update and insert

[Olaf Stein]

Agreed...
Also for consistency's sake


On 9/4/07 3:15 PM, Michael Dykman [EMAIL PROTECTED] wrote:

 Triggers are a fine idea, but I would use a trigger for both cases..
 no point putting that level of housekeeping on the application when
 you can set rules in the database and more or less forget about it.
 
  - michael
 
 
 On 9/4/07, Olaf Stein [EMAIL PROTECTED] wrote:
 I would use a trigger (at least for the update)
 
 The first insert should work with now() and you can leave lastupdateted
 empty
 
 
 Olaf
 
 
 On 9/4/07 3:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:
 
 Hi list,
 
 i tried to create a table with inserted  lastupdated timestamp fields:
 
 create table temp (
 id int not null primary ke auto_increment,
 data varchar(100),
 inserted timestamp default now(),
 lastupdated timestamp(8));
 
 
 how do i get mysql to put in the current timestamp for inserted 
 lastupdated fields when i insert a record and only lastupdated when i
 update the record?
 
 thanks,
 T. Hiep
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timestamp for update and insert

[Hiep Nguyen]

is it possible to do without trigger?

i google and found this link:
http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html

but when i tried to combine two examples into one CREATE statement and it 
didn't work.


any idea?

is there a way to create this table that accomplishes these two goals?

thanks,
T. Hiep

On Tue, 4 Sep 2007, Michael Dykman wrote:


Triggers are a fine idea, but I would use a trigger for both cases..
no point putting that level of housekeeping on the application when
you can set rules in the database and more or less forget about it.

- michael


On 9/4/07, Olaf Stein [EMAIL PROTECTED] wrote:

I would use a trigger (at least for the update)

The first insert should work with now() and you can leave lastupdateted
empty


Olaf


On 9/4/07 3:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:


Hi list,

i tried to create a table with inserted  lastupdated timestamp fields:

create table temp (
id int not null primary ke auto_increment,
data varchar(100),
inserted timestamp default now(),
lastupdated timestamp(8));


how do i get mysql to put in the current timestamp for inserted 
lastupdated fields when i insert a record and only lastupdated when i
update the record?

thanks,
T. Hiep




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





--
- michael dykman
- [EMAIL PROTECTED]

- All models are wrong.  Some models are useful.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timestamp for update and insert

[Michael Dykman]

There is nothing terribly wrong with the approach documented in
'http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html' but, as
you no doubt have read, it does mean that you have to make sure that
every insert statement is specifically designed to set the *second*
timestamp field to now() and then count on the built-in properties to
see the first one updated on every UPDATE.

The only other caveats are:
your application behaviour is now dependent on the ordering of
columns; ok in the short-term, increasingly annoying over time as
maintainence phases grow the app in complexity.
   importing data from your system to another system might prove
hairy as you figure out how to temporarily avoid this bevahiour to
keep your data intact.

The trigger method is universal in that this solution will port to any
half-way reasonable database engine

but, as in all things IT, do whatever best meets your circumstances.

 - michael

On 9/4/07, Hiep Nguyen [EMAIL PROTECTED] wrote:
 is it possible to do without trigger?

 i google and found this link:
 http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html

 but when i tried to combine two examples into one CREATE statement and it
 didn't work.

 any idea?

 is there a way to create this table that accomplishes these two goals?

 thanks,
 T. Hiep

 On Tue, 4 Sep 2007, Michael Dykman wrote:

  Triggers are a fine idea, but I would use a trigger for both cases..
  no point putting that level of housekeeping on the application when
  you can set rules in the database and more or less forget about it.
 
  - michael
 
 
  On 9/4/07, Olaf Stein [EMAIL PROTECTED] wrote:
  I would use a trigger (at least for the update)
 
  The first insert should work with now() and you can leave lastupdateted
  empty
 
 
  Olaf
 
 
  On 9/4/07 3:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:
 
  Hi list,
 
  i tried to create a table with inserted  lastupdated timestamp fields:
 
  create table temp (
  id int not null primary ke auto_increment,
  data varchar(100),
  inserted timestamp default now(),
  lastupdated timestamp(8));
 
 
  how do i get mysql to put in the current timestamp for inserted 
  lastupdated fields when i insert a record and only lastupdated when i
  update the record?
 
  thanks,
  T. Hiep
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 
  --
  - michael dykman
  - [EMAIL PROTECTED]
 
  - All models are wrong.  Some models are useful.
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 



-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: timestamp for update and insert

[Daevid Vincent]

Just do this...

create table temp (
 id int not null primary key auto_increment,
 data varchar(100),
 inserted timestamp default 0,
 lastupdated default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 
And just use 

Insert into temp (inserted ) values (NOW());

You're only inserting once, so just do it in the code.

Then no need for triggers -- what a waste. Mysql will update the lastupdated
field.

Note the 'default 0' that is important... When you have multiple timestamp
columns and want one to be auto handled. 

D.Vin

 -Original Message-
 From: Hiep Nguyen [mailto:[EMAIL PROTECTED] 
 Sent: Tuesday, September 04, 2007 12:02 PM
 To: mysql@lists.mysql.com
 Subject: timestamp for update and insert
 
 Hi list,
 
 i tried to create a table with inserted  lastupdated 
 timestamp fields:
 
 create table temp (
 id int not null primary ke auto_increment,
 data varchar(100),
 inserted timestamp default now(),
 lastupdated timestamp(8));
 
 
 how do i get mysql to put in the current timestamp for inserted  
 lastupdated fields when i insert a record and only lastupdated when i 
 update the record?
 
 thanks,
 T. Hiep
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timestamp for update and insert

[Hiep Nguyen]

so, if trigger is used then

create table temp (
id int not null primary key auto_increment,
data varchar(100),
inserted timestamp,
lastupdated timestamp)

is good enough, right?  trigger will use now() function to set inserted  
lastupdated.


any thought on backup  restore tables  tringgers???

thank you for your helps.
T. Hiep

On Tue, 4 Sep 2007, Michael Dykman wrote:


There is nothing terribly wrong with the approach documented in
'http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html' but, as
you no doubt have read, it does mean that you have to make sure that
every insert statement is specifically designed to set the *second*
timestamp field to now() and then count on the built-in properties to
see the first one updated on every UPDATE.

The only other caveats are:
   your application behaviour is now dependent on the ordering of
columns; ok in the short-term, increasingly annoying over time as
maintainence phases grow the app in complexity.
  importing data from your system to another system might prove
hairy as you figure out how to temporarily avoid this bevahiour to
keep your data intact.

The trigger method is universal in that this solution will port to any
half-way reasonable database engine

but, as in all things IT, do whatever best meets your circumstances.

- michael

On 9/4/07, Hiep Nguyen [EMAIL PROTECTED] wrote:

is it possible to do without trigger?

i google and found this link:
http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html

but when i tried to combine two examples into one CREATE statement and it
didn't work.

any idea?

is there a way to create this table that accomplishes these two goals?

thanks,
T. Hiep

On Tue, 4 Sep 2007, Michael Dykman wrote:


Triggers are a fine idea, but I would use a trigger for both cases..
no point putting that level of housekeeping on the application when
you can set rules in the database and more or less forget about it.

- michael


On 9/4/07, Olaf Stein [EMAIL PROTECTED] wrote:

I would use a trigger (at least for the update)

The first insert should work with now() and you can leave lastupdateted
empty


Olaf


On 9/4/07 3:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:


Hi list,

i tried to create a table with inserted  lastupdated timestamp fields:

create table temp (
id int not null primary ke auto_increment,
data varchar(100),
inserted timestamp default now(),
lastupdated timestamp(8));


how do i get mysql to put in the current timestamp for inserted 
lastupdated fields when i insert a record and only lastupdated when i
update the record?

thanks,
T. Hiep




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





--
- michael dykman
- [EMAIL PROTECTED]

- All models are wrong.  Some models are useful.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







--
- michael dykman
- [EMAIL PROTECTED]

- All models are wrong.  Some models are useful.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timestamp for update and insert

[Olaf Stein]

If you decide to use the trigger here is the syntax

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

And that table structure looks ok to me


As far as the backup goes just dump the mysql database, which you should be
doing anyway to backup users etc

Olaf



On 9/4/07 3:59 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:

 so, if trigger is used then
 
 create table temp (
 id int not null primary key auto_increment,
 data varchar(100),
 inserted timestamp,
 lastupdated timestamp)
 
 is good enough, right?  trigger will use now() function to set inserted 
 lastupdated.
 
 any thought on backup  restore tables  tringgers???
 
 thank you for your helps.
 T. Hiep
 
 On Tue, 4 Sep 2007, Michael Dykman wrote:
 
 There is nothing terribly wrong with the approach documented in
 'http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html' but, as
 you no doubt have read, it does mean that you have to make sure that
 every insert statement is specifically designed to set the *second*
 timestamp field to now() and then count on the built-in properties to
 see the first one updated on every UPDATE.
 
 The only other caveats are:
your application behaviour is now dependent on the ordering of
 columns; ok in the short-term, increasingly annoying over time as
 maintainence phases grow the app in complexity.
   importing data from your system to another system might prove
 hairy as you figure out how to temporarily avoid this bevahiour to
 keep your data intact.
 
 The trigger method is universal in that this solution will port to any
 half-way reasonable database engine
 
 but, as in all things IT, do whatever best meets your circumstances.
 
 - michael
 
 On 9/4/07, Hiep Nguyen [EMAIL PROTECTED] wrote:
 is it possible to do without trigger?
 
 i google and found this link:
 http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html
 
 but when i tried to combine two examples into one CREATE statement and it
 didn't work.
 
 any idea?
 
 is there a way to create this table that accomplishes these two goals?
 
 thanks,
 T. Hiep
 
 On Tue, 4 Sep 2007, Michael Dykman wrote:
 
 Triggers are a fine idea, but I would use a trigger for both cases..
 no point putting that level of housekeeping on the application when
 you can set rules in the database and more or less forget about it.
 
 - michael
 
 
 On 9/4/07, Olaf Stein [EMAIL PROTECTED] wrote:
 I would use a trigger (at least for the update)
 
 The first insert should work with now() and you can leave lastupdateted
 empty
 
 
 Olaf
 
 
 On 9/4/07 3:01 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:
 
 Hi list,
 
 i tried to create a table with inserted  lastupdated timestamp fields:
 
 create table temp (
 id int not null primary ke auto_increment,
 data varchar(100),
 inserted timestamp default now(),
 lastupdated timestamp(8));
 
 
 how do i get mysql to put in the current timestamp for inserted 
 lastupdated fields when i insert a record and only lastupdated when i
 update the record?
 
 thanks,
 T. Hiep
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 
 --
 - michael dykman
 - [EMAIL PROTECTED]
 
 - All models are wrong.  Some models are useful.
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 
 
 -- 
 - michael dykman
 - [EMAIL PROTECTED]
 
 - All models are wrong.  Some models are useful.
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: General Query Log -- No Timestamp

[Alex Greg]

On 6/26/07, Brown, Charles [EMAIL PROTECTED] wrote:


The MYSQL general query log does not include timestamp of queries that
it logs because queries are logged many many seconds before they are
executed.


Which version of MySQL are you running? I'm running 5.0.22 on my
desktop, but I'm fairly sure that older versions included timestamps
in the general query log.

I just enabled mine to test this (by adding log = /var/log/mysql.log
to /etc/my.cnf) and it looks something like:

070629  8:17:44   6 Connect [EMAIL PROTECTED] on monitoring
 6 Query   set autocommit=0
 6 Query   select * from urls where active=y
070629  8:17:45   6 Query   INSERT INTO results VALUES
(NULL,5,now(),0.5833,35267)
 6 Query   INSERT INTO results VALUES
(NULL,6,now(),0.0137,0)
 6 Query   INSERT INTO results VALUES
(NULL,8,now(),0.7762,28130)
 6 Query   INSERT INTO results VALUES
(NULL,9,now(),0.0348,4217)

-- Alex

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

General Query Log -- No Timestamp

[Brown, Charles]

The MYSQL general query log does not include timestamp of queries that
it logs because queries are logged many many seconds before they are
executed. Can someone help me associated queries found in the query log
with wall clock? I am trying to get a list of queries that were executed
within a given timeslot. I would like to get the description of these
queries so that I can give to my webmaster. Based on the description of
these queries, he will be able to determine which area the application
is causing this on going looping problem that generates 5000 request in
5 mins. Our normal request volume is about 50/min. Can someone help me?
Without the timestamp, I wouldn't be able track or identify the queries
that came in during the problem time frame. With all things considered,
MySQL should be able to append timestamp as it writes these General
query records -- I would think.

Thanks

This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Avoiding TIMESTAMP

[Miguel Cardenas]

Hello list

I need to solve a little problem but don't mind how, maybe you could suggest 
something.

I have a database which registers payments, records have a AUTO_INCREMENT 
field to assign a unique consecutive number associated to every payment, some 
other fields that store the amount, type of payment, etc. and another 
TIMESTAMP field that stores the date and time.

One of the fields contains the username for who received the payment and it is 
stored automatically by the software, so I can know who processed the 
payment, when, and all related information...

In the first stage of the system it worked only for 'localhost' and a unique 
test user, so all my current payments have registered that user. Now the 
system is able to select a host and a user, so I want to update the test user 
username for a real user, the database has already some records and would not 
like to reenter them by hand logging in as a real user.

My problem is this... if I update the username field, the TIMESTAMP updates 
the date and time of the payment to the current values and the payment 
date/time does not coincide then...

Is there a way to update only that one field avoiding the TIMESTAMP update? 
One way is to update both fields, specifying the user and the same date/time 
by hand, but comes a new problem, payments have different date and time, so I 
would need to create a small routine to update records one by one with its 
particular date/time, but maybe there is another way to do it with a single 
command...

Thanks for any comment


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Avoiding TIMESTAMP

[Tim Lucia]

 -Original Message-
 From: Miguel Cardenas [mailto:[EMAIL PROTECTED]
 Sent: Saturday, May 26, 2007 8:21 AM
 To: mysql@lists.mysql.com
 Subject: Avoiding TIMESTAMP
 
 Hello list
 
 I need to solve a little problem but don't mind how, maybe you could
 suggest
 something.
 
 I have a database which registers payments, records have a AUTO_INCREMENT
 field to assign a unique consecutive number associated to every payment,
 some
 other fields that store the amount, type of payment, etc. and another
 TIMESTAMP field that stores the date and time.
 
 One of the fields contains the username for who received the payment and
 it is
 stored automatically by the software, so I can know who processed the
 payment, when, and all related information...
 
 In the first stage of the system it worked only for 'localhost' and a
 unique
 test user, so all my current payments have registered that user. Now the
 system is able to select a host and a user, so I want to update the test
 user
 username for a real user, the database has already some records and would
 not
 like to reenter them by hand logging in as a real user.
 
 My problem is this... if I update the username field, the TIMESTAMP
 updates
 the date and time of the payment to the current values and the payment
 date/time does not coincide then...

Are you inserting null into the timestamp column, either explicitly or
implicitly (like with a trigger)?  If I don't specify the timestamp column,
as shown below, it leaves it alone.  Using MyISAM on 5.0.24-NT, it works as
you want.  Assuming I've inferred your table definition, that is.

drop table if exists payment;
create table payment (
  id integer not null AUTO_INCREMENT primary key,
  username varchar(255) not null,
  amount FIXED(10,2),
  txtime timestamp not null
);

insert into payment (username, amount) values ('tjl', 1234567.89);
select * from payment;

-- note time
-- wait a little while

update payment set username='aal';
select * from payment;
-- same time...

update payment set username='tjl', txtime=null;
select * from payment;
-- updated time

Tim


 
 Is there a way to update only that one field avoiding the TIMESTAMP
 update?
 One way is to update both fields, specifying the user and the same
 date/time
 by hand, but comes a new problem, payments have different date and time,
 so I
 would need to create a small routine to update records one by one with its
 particular date/time, but maybe there is another way to do it with a
 single
 command...
 
 Thanks for any comment
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

problem with timestamp data type

[Alessandro Agostini]

Hi to all,
I have a strange error on using of timestamp data type.
See the follow table definition and insert command.

DROP TABLE IF EXISTS Storicocorrenti;
CREATE TABLE Storicocorrenti (
 ID_LINEA mediumint(6) unsigned zerofill NOT NULL default '00',
 DATAORA timestamp NOT NULL default '-00-00 00:00:00',
 DFASEABS smallint(3) default NULL,
 TENSIONE decimal(9,2) default NULL,
 POTATT decimal(7,3) default NULL,
 POTREA decimal(7,3) default NULL,
 DFASEVI smallint(3) default NULL,
 CORRENTE decimal(6,2) default NULL,
 PRIMARY KEY  (ID_LINEA,DATAORA)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Storicocorrenti VALUES (032100, '2005-03-27 01:00:00', 0, 
'40.00', '0.000', '0.000', 0, '0.00');
INSERT INTO Storicocorrenti VALUES (032100, '2005-03-27 02:00:00', 0, 
'40.00', '0.000', '0.000', 0, '0.00');


Watching the table content, I see second record as:

032100, '2005-03-27 03:00:00', 0, '40.00', '0.000', '0.000', 0, '0.00'

MySql change the time information of DATAORA field from 02:00:00 to 
03:00:00.

This error is reproducible on various version of mysql. I have tried it on 3.23, 
4.1, 5.0


On Postgres DB all is ok. I have similar record at same time but other date and 
in these case the problem is not present.


Can you help me to resolve it?
Thanks in advance

Alessandro Agostini
IFAC - CNR
Italy


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: problem with timestamp data type

[Baron Schwartz]

Hello Allesandro,

Alessandro Agostini wrote:

Hi to all,
I have a strange error on using of timestamp data type.
See the follow table definition and insert command.

DROP TABLE IF EXISTS Storicocorrenti;
CREATE TABLE Storicocorrenti (
 ID_LINEA mediumint(6) unsigned zerofill NOT NULL default '00',
 DATAORA timestamp NOT NULL default '-00-00 00:00:00',
 DFASEABS smallint(3) default NULL,
 TENSIONE decimal(9,2) default NULL,
 POTATT decimal(7,3) default NULL,
 POTREA decimal(7,3) default NULL,
 DFASEVI smallint(3) default NULL,
 CORRENTE decimal(6,2) default NULL,
 PRIMARY KEY  (ID_LINEA,DATAORA)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Storicocorrenti VALUES (032100, '2005-03-27 01:00:00', 0, 
'40.00', '0.000', '0.000', 0, '0.00');
INSERT INTO Storicocorrenti VALUES (032100, '2005-03-27 02:00:00', 0, 
'40.00', '0.000', '0.000', 0, '0.00');


Watching the table content, I see second record as:

032100, '2005-03-27 03:00:00', 0, '40.00', '0.000', '0.000', 0, '0.00'

MySql change the time information of DATAORA field from 02:00:00 to 
03:00:00.


This error is reproducible on various version of mysql. I have tried it 
on 3.23, 4.1, 5.0


I can't reproduce this on version 5.0.38 or 5.0.24.  Perhaps it is something to do with 
the server timezone, the connection timezone, or the combination of the two?


Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: problem with timestamp data type

[Joerg Bruehe]

Hi Alessandro, all !

Baron Schwartz wrote:

Hello Allesandro,

Alessandro Agostini wrote:

[[...]]

Watching the table content, I see second record as:

032100, '2005-03-27 03:00:00', 0, '40.00', '0.000', '0.000', 0, 
'0.00'


MySql change the time information of DATAORA field from 02:00:00 to 
03:00:00.


This error is reproducible on various version of mysql. I have tried 
it on 3.23, 4.1, 5.0


I can't reproduce this on version 5.0.38 or 5.0.24.  Perhaps it is 
something to do with the server timezone, the connection timezone, or 
the combination of the two?


In 2005, March 27 was a Sunday, the last one in March.
On this day at 02:00, summer time started in (at least: western) Europe, 
so '2005-03-27 02:00:00' and '2005-03-27 03:00:00' are really the same 
moment in Italian time (which I assume your machines are running on).



HTH,
Jörg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: problem with timestamp data type

[David Lombardi]

Maybe, it has to do with the TIMESTAMP behavior: whenever you insert a 
TIMESTAMP value MySQL converts it to UTC and converts it back to local time (or 
the time you entered).

To prevent this behavior you can
- change TIMESTAMP into DATETIME or 
- disable it by adding this setting to the MY.INI configuration file: 
default-time-zone=+00:00

It both cases your existing values have been not stored as you expect so you 
have to fix them accordingly (things are rather difficult if you are dealing 
with data acquired in different TZ and/or with/without daylight saving mode.
However if the data is stored and fetched always in the same time-zone (it was 
not my case, as we had to move databases between systems), you are safe as you 
are now.

David

-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Alessandro Agostini
Sent: giovedì 24 maggio 2007 14.28
To: mysql@lists.mysql.com
Subject: problem with timestamp data type

Hi to all,
I have a strange error on using of timestamp data type.
See the follow table definition and insert command.

DROP TABLE IF EXISTS Storicocorrenti;
CREATE TABLE Storicocorrenti (
  ID_LINEA mediumint(6) unsigned zerofill NOT NULL default '00',
  DATAORA timestamp NOT NULL default '-00-00 00:00:00',
  DFASEABS smallint(3) default NULL,
  TENSIONE decimal(9,2) default NULL,
  POTATT decimal(7,3) default NULL,
  POTREA decimal(7,3) default NULL,
  DFASEVI smallint(3) default NULL,
  CORRENTE decimal(6,2) default NULL,
  PRIMARY KEY  (ID_LINEA,DATAORA)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Storicocorrenti VALUES (032100, '2005-03-27 01:00:00', 0, 
'40.00', '0.000', '0.000', 0, '0.00');
INSERT INTO Storicocorrenti VALUES (032100, '2005-03-27 02:00:00', 0, 
'40.00', '0.000', '0.000', 0, '0.00');

Watching the table content, I see second record as:

032100, '2005-03-27 03:00:00', 0, '40.00', '0.000', '0.000', 0, '0.00'

MySql change the time information of DATAORA field from 02:00:00 to 
03:00:00.

This error is reproducible on various version of mysql. I have tried it on 
3.23, 
4.1, 5.0

On Postgres DB all is ok. I have similar record at same time but other date and 
in these case the problem is not present.

Can you help me to resolve it?
Thanks in advance

Alessandro Agostini
IFAC - CNR
Italy


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: string to timestamp conversion

[Mogens Melander]

Like:

  str_to_date('Thu May 17 09:15:47 2007','%a %b %e %T %Y')

On Mon, May 21, 2007 21:10, [EMAIL PROTECTED] wrote:
 Have you considered using the string to time function?


 Sent via BlackBerry from T-Mobile

 -Original Message-
 From: Bryan Cantwell [EMAIL PROTECTED]
 Date: Mon, 21 May 2007 12:08:11
 To:MySQL General mysql@lists.mysql.com
 Subject: string to timestamp conversion

 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:http://lists.mysql.com/[EMAIL PROTECTED]




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


 --
 This message has been scanned for viruses and
 dangerous content by OpenProtect(http://www.openprotect.com), and is
 believed to be clean.




-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

string to timestamp conversion

[Bryan Cantwell]

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:http://lists.mysql.com/[EMAIL PROTECTED]

Re: string to timestamp conversion

[seungp]

Have you considered using the string to time function?


Sent via BlackBerry from T-Mobile  

-Original Message-
From: Bryan Cantwell [EMAIL PROTECTED]
Date: Mon, 21 May 2007 12:08:11 
To:MySQL General mysql@lists.mysql.com
Subject: string to timestamp conversion

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:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: string to timestamp conversion

[Bryan Cantwell]

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 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:http://lists.mysql.com/[EMAIL PROTECTED]

RE: string to timestamp conversion

[Chris Boget]

Take a look at the following:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#func
tion_unix-timestamp

That looks like exactly what you need...

thnx,
Chris 

-Original Message-
From: Bryan Cantwell [mailto:[EMAIL PROTECTED] 
Sent: Monday, May 21, 2007 4:45 PM
To: MySQL General
Subject: RE: string to timestamp conversion

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 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:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to replicate a timestamp field

[Maciej Dobrzanski]

In news:[EMAIL PROTECTED],
Bruno Rodrigues Silva [EMAIL PROTECTED] wrote:

 this field (The master is dep= loyed Europe and the slave in Brazil).
 The time of fly to replicate the que= ry that leave the master and go
 to slave can be more than 1 second. Therefo= re, when this query is
 executed in slave server, the timestamp value will b= e different of
 the master server one.

No, it will not be a different value. The replicated query is preceded by 
SET TIMESTAMP statement which informs slave of the execution time on the 
master server. If row-based replication is enabled, the problem does not 
even exist, because the entire updated row contents is replicated. In either 
case it does not matter if the slave is one second or one year behind the 
master server.

MySQL replication is asynchronous and therefore the delays between execution 
times are at the foundation of the functionality.

Maciek



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to replicate a timestamp field

[Bruno Rodrigues Silva]

Hello.


I know that when a timestamp field has default current_timestamp on update 
current_timestamp, it will be updated for each update.  Question: I have a 
slave server which replicate this field (The master is deployed Europe and the 
slave in Brazil). The time of fly to replicate the query that leave the master 
and go to slave can be more than 1 second. Therefore, when this query is 
executed in slave server, the timestamp value will be different of the master 
server one. Is there some way to fix it? Because, The tuples must have the same 
value in both Master and Slave.

Regards

Bruno Silva




__
Fale com seus amigos  de graça com o novo Yahoo! Messenger 
http://br.messenger.yahoo.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP

[Nuno Oliveira]

The second major problem I am running into after the upgrade is the
following error, which did not occur on Development.

Error Executing Database Query. Cannot convert value '-00-00
00:00:00'
from column 4 to TIMESTAMP.
The error occurred on line 8.
MySQL version is: 5.0.27

Thanks in advance for any help.



I don't know what/how are you doing and I don't even know enough about the 
subject but:



From PHP manual @ http://pt.php.net/manual/en/function.strtotime.php:
Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 
GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond 
to the minimum and maximum values for a 32-bit signed integer.) Additionally, 
not all platforms support negative timestamps, therefore your date range 
may be limited to no earlier than the Unix epoch. This means that e.g. dates 
prior to Jan 1, 1970 will not work on Windows, some Linux distributions, 
and a few other operating systems. PHP 5.1.0 and newer versions overcome 
this limitation though.



From MySQL manual @ 
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html:
The range is '1970-01-01 00:00:01' UTC to partway through the year 2037. 
TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 
00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00' 
because that is equivalent to 0 seconds from the epoch and the value 0 is 
reserved for representing '-00-00 00:00:00', the “zero” TIMESTAMP value.


HTH

- Nuno



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP

[Taco Fleur]

 I'm simply executing a SELECT query that used to work before, same
database, everything the same except a higher version of the MySQL server.

-Original Message-
From: Nuno Oliveira [mailto:[EMAIL PROTECTED] 
Sent: Monday, 22 January 2007 7:33 PM
To: mysql@lists.mysql.com
Subject: Re: Cannot convert value '-00-00 00:00:00' from column 4 to
TIMESTAMP

 The second major problem I am running into after the upgrade is the 
 following error, which did not occur on Development.
 
 Error Executing Database Query. Cannot convert value '-00-00 
 00:00:00'
 from column 4 to TIMESTAMP.
 The error occurred on line 8.
 MySQL version is: 5.0.27
 
 Thanks in advance for any help.
 

I don't know what/how are you doing and I don't even know enough about the
subject but:

From PHP manual @ http://pt.php.net/manual/en/function.strtotime.php:
Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901
20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that
correspond to the minimum and maximum values for a 32-bit signed integer.)
Additionally, not all platforms support negative timestamps, therefore your
date range may be limited to no earlier than the Unix epoch. This means that
e.g. dates prior to Jan 1, 1970 will not work on Windows, some Linux
distributions, and a few other operating systems. PHP 5.1.0 and newer
versions overcome this limitation though.

From MySQL manual @
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html:
The range is '1970-01-01 00:00:01' UTC to partway through the year 2037. 
TIMESTAMP values are stored as the number of seconds since the epoch
('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value
'1970-01-01 00:00:00' 
because that is equivalent to 0 seconds from the epoch and the value 0 is
reserved for representing '-00-00 00:00:00', the zero TIMESTAMP value.

HTH

 - Nuno



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP

[Nuno Oliveira]

I'm simply executing a SELECT query that used to work before, same
database, everything the same except a higher version of the MySQL
server.


In that case you are probably facing some uncompatible issue between the 
versions you used.


Maybe someone else is able to help you more that I :(

Sorry



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP

[tere]

This is because with version 4.1 and higher the format of timestamp
changes to -00-00 00:00:00. We had this problem, so we had to add a
few lines in the scripts that take this field and work with it

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP

[Taco Fleur]

 
Hi thanks for replying.
I can't immediately see the difference between your sample and mine, except
the single quotes are missing, is that it?
Thanks in advance.
-Original Message-
From: tere [mailto:[EMAIL PROTECTED] 
Sent: Monday, 22 January 2007 9:12 PM
To: mysql@lists.mysql.com
Subject: RE: Cannot convert value '-00-00 00:00:00' from column 4 to
TIMESTAMP

This is because with version 4.1 and higher the format of timestamp changes
to -00-00 00:00:00. We had this problem, so we had to add a few lines in
the scripts that take this field and work with it

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP

[Taco Fleur]

 
The second major problem I am running into after the upgrade is the
following error, which did not occur on Development. 

Error Executing Database Query. Cannot convert value '-00-00 00:00:00'
from column 4 to TIMESTAMP. 
The error occurred on line 8. 

MySQL version is: 5.0.27

Thanks in advance for any help.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

upgrading from mysql 4.0 to 4.1 : TIMESTAMP OTHER FEATURES

[tere]

Hi! I would like to upgrade the database of my organisation from
mysql4.0.22 from 4.1. We use Debian.
I've read info in the manual, but i don't have things clear.

We process data of DB with scripts, and  I'm annoyed because the change
of format  of timestamp, is there any command in 4.1 to obtain  this
info in the previous format (4.0) ??? I want that out in 4.1 as
MMDDHHMMSS

Furthermore, my databases are in latin1, i've read that 4.1 take data in
utf8, but i don't understand reading manual how this affect to my info.

And to finish, do i have to keep more features in mind

Thanks

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: upgrading from mysql 4.0 to 4.1 : TIMESTAMP OTHER FEATURES

[Dan Buettner]

Regarding the format of TIMESTAMP columns, one of the user comments on
http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
offers the solution below:

Posted by Kjell Arne Rekaa on April 14 2005 11:11pm
If you want the same view of a timestamp field in 4.1.x as it was in
in earlier mysql versions, without the delimiter characters in date
and time, simply add a +0 to the column name:
mysql create table date (remember timestamp);
Query OK, 0 rows affected (0.03 sec)
mysql insert date values ('2005051712');
Query OK, 1 row affected (0.00 sec)
mysql select remember from date;
+-+
| remember|
+-+
| 2005-05-17 12:00:00 |
+-+
1 row in set (0.00 sec)
mysql select remember+0 from date;
++
| remember+0 |
++
| 2005051712 |
++
1 row in set (0.00 sec)

As far as your character set, I believe that is stored by table, so
your data should remain OK.

Another major consideration with 4.1 is that passwords changed
considerably from 4.0 to 4.1:
http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html

HTH,
Dan



On 12/20/06, tere [EMAIL PROTECTED] wrote:

Hi! I would like to upgrade the database of my organisation from
mysql4.0.22 from 4.1. We use Debian.
I've read info in the manual, but i don't have things clear.

We process data of DB with scripts, and  I'm annoyed because the change
of format  of timestamp, is there any command in 4.1 to obtain  this
info in the previous format (4.0) ??? I want that out in 4.1 as
MMDDHHMMSS

Furthermore, my databases are in latin1, i've read that 4.1 take data in
utf8, but i don't understand reading manual how this affect to my info.

And to finish, do i have to keep more features in mind

Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Is a set current TIMESTAMP operation atomic when updating/inserting multiple rows?

[Dan Jakubiec]

Hi.  I want to insert/update multiple rows into a table which has a
timestamp field, and I want to set the timestamp field in each row to the
current timestamp.  However, it is important to me that all the rows I
update actually end up with the same timestamp value.

My concern is: what happens if the SQL query take a long time and the
current timestamp crosses a second boundary?  For example, suppose I issue
the following request on a very large table:

 UPDATE ts SET my_timestamp=NULL;

Say that this query takes 3 seconds to complete.  My questions:

1) Will all the rows have the same timestamp value?  Or will some rows have
now, now+1, and now+2?

2) Will the behavior be different if I use my_timestamp=NOW()?

3) If the timestamps will be different, what's the best way to make them all
the same?

Thanks in advance,

--
Dan Jakubiec

Re: Is a set current TIMESTAMP operation atomic when updating/inserting multiple rows?

[Chris W]

Dan Jakubiec wrote:


Hi.  I want to insert/update multiple rows into a table which has a
timestamp field, and I want to set the timestamp field in each row to the
current timestamp.  However, it is important to me that all the rows I
update actually end up with the same timestamp value.

My concern is: what happens if the SQL query take a long time and the
current timestamp crosses a second boundary?  For example, suppose I 
issue

the following request on a very large table:

 UPDATE ts SET my_timestamp=NULL;

Say that this query takes 3 seconds to complete.  My questions:

1) Will all the rows have the same timestamp value?  Or will some rows 
have

now, now+1, and now+2?

2) Will the behavior be different if I use my_timestamp=NOW()?

3) If the timestamps will be different, what's the best way to make 
them all

the same?



In the manual it says that all calls to now() in a query always return 
the same time regardless of how many there are and how long it takes the 
query to run so I am thinking that all records with an auto update time 
stamp column that get changed in a single query would all have the same 
time stamp.  It should be easy enough to test if you have a large set of 
data where an update would take a long time.  I'm pretty sure it will do 
what you need though and set them all the same.


--
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want  
give the gifts they want
One stop wish list for any gift, 
from anywhere, for any occasion!

http://thewishzone.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Is a set current TIMESTAMP operation atomic when updating/inserting multiple rows?

[Dan Jakubiec]

Ah, thanks Chris.  I should have looked there (I was studying the TIMESTAMP
sections, not NOW()).  The NOW() documentation also refers to the SYSDATE()
function which does the opposite (i.e. sets the time when the record
actually gets added).

So I'll go ahead and use the 'my_timestamp=NOW()' format in my queries.  Out
of curiosity, I wonder which of the two approaches 'my_timestamp=NULL'
uses...  NOW() or SYSDATE()?

-- Dan

In the manual it says that all calls to now() in a query always return

the same time regardless of how many there are and how long it takes the
query to run so I am thinking that all records with an auto update time
stamp column that get changed in a single query would all have the same
time stamp.  It should be easy enough to test if you have a large set of
data where an update would take a long time.  I'm pretty sure it will do
what you need though and set them all the same.

Re: Is a set current TIMESTAMP operation atomic when updating/inserting multiple rows?

[Dilipkumar]

Hi,

If u update this statment like what u have mentioned it will be updated for 
all the rows.


update tablename set field name='2006-07-07 00:00:00';

Thanks  Regards
Dilipkumar
- Original Message - 
From: Chris W [EMAIL PROTECTED]
To: Dan Jakubiec [EMAIL PROTECTED]; MYSQL General List 
mysql@lists.mysql.com

Sent: Monday, July 24, 2006 1:11 AM
Subject: Re: Is a set current TIMESTAMP operation atomic when 
updating/inserting multiple rows?




Dan Jakubiec wrote:


Hi.  I want to insert/update multiple rows into a table which has a
timestamp field, and I want to set the timestamp field in each row to the
current timestamp.  However, it is important to me that all the rows I
update actually end up with the same timestamp value.

My concern is: what happens if the SQL query take a long time and the
current timestamp crosses a second boundary?  For example, suppose I 
issue

the following request on a very large table:

 UPDATE ts SET my_timestamp=NULL;

Say that this query takes 3 seconds to complete.  My questions:

1) Will all the rows have the same timestamp value?  Or will some rows 
have

now, now+1, and now+2?

2) Will the behavior be different if I use my_timestamp=NOW()?

3) If the timestamps will be different, what's the best way to make them 
all

the same?



In the manual it says that all calls to now() in a query always return the 
same time regardless of how many there are and how long it takes the query 
to run so I am thinking that all records with an auto update time stamp 
column that get changed in a single query would all have the same time 
stamp.  It should be easy enough to test if you have a large set of data 
where an update would take a long time.  I'm pretty sure it will do what 
you need though and set them all the same.


--
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want  give the gifts they want
One stop wish list for any gift, from anywhere, for any occasion!
http://thewishzone.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]




** DISCLAIMER **
Information contained and transmitted by this E-MAIL is proprietary to 
Sify Limited and is intended for use only by the individual or entity to 
which it is addressed, and may contain information that is privileged, 
confidential or exempt from disclosure under applicable law. If this is a 
forwarded message, the content of this E-MAIL may not have been sent with 
the authority of the Company. If you are not the intended recipient, an 
agent of the intended recipient or a  person responsible for delivering the 
information to the named recipient,  you are notified that any use, 
distribution, transmission, printing, copying or dissemination of this 
information in any way or in any manner is strictly prohibited. If you have 
received this communication in error, please delete this mail  notify us 
immediately at [EMAIL PROTECTED]



Watch the latest updates on Mumbai, with video coverage of news, events,
Bollywood, live darshan from Siddhivinayak temple and more, only on
www.mumbailive.in

Watch the hottest videos from Bollywood, Fashion, News and more only on
www.sifymax.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: automatic timestamp

[Nestor]

Scott,

I do not think that is how it works under revision 4.0.21.

Thanks,

Néstor :-)

On 7/5/06, Scott Haneda [EMAIL PROTECTED] wrote:

 People,

 I have the following table:
 DROP TABLE IF EXISTS bid;
 CREATE TABLE bid (
   bid_id int(11) NOT NULL auto_increment,
   bid_proj_name varchar(100) NOT NULL default '',
   bid_prop_name varchar(100) NOT NULL default '',
   bid_amount varchar(20) NULL default '',
   bid_sub_name varchar(100) NOT NULL default '',
   bid_sub_desc varchar(100) default '',
   bid_sub_amount varchar(20) NULL default '',
   bid_winner tinyint(1) NOT NULL default '0',
   bid_date TIMESTAMP,
   PRIMARY KEY  (bid_id),
   UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name)
 ) TYPE=MyISAM;


 and I am trying toload a file with the following data:
 ,Construction Management,RCG Consulting,,Orly-Belle
 ,Construction Management,RCG Consulting,,American Engineers

 I am using phpmyadmin and the date always gets enter as 00

 What do I need to do to force the current time stamp?
 I am using mysql version 4.0.21-standard

IIRC, you need two timestamp fields for auto to work, default timestamps for
the first timestamp column will be ignored, but the first column will get
auto timestamp in a table that has more than one timestamp column.

I usually set up two, updated and added, and I always set the added field to
NOW()

It explains it pretty well in the docs here
http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: automatic timestamp

[Nestor]

Chris,

my situation is that I am reading a file using phpmyadmin and whne I do not add
the extra , then I get an invalid number of records, so I have to
have the extra ,

When I do an insert without the last comma, it does set the correct day.
I do not know that it will work if I add the fields that I am
importing via phpmyadmin

I guess I will just do it that way.  When I am importing a file just
add the field names
that I am bringing in.

Thanks to all,

Néstor :-)

On 7/5/06, Chris [EMAIL PROTECTED] wrote:

Nestor wrote:
 People,

 I have the following table:
 DROP TABLE IF EXISTS bid;
 CREATE TABLE bid (
  bid_id int(11) NOT NULL auto_increment,
  bid_proj_name varchar(100) NOT NULL default '',
  bid_prop_name varchar(100) NOT NULL default '',
  bid_amount varchar(20) NULL default '',
  bid_sub_name varchar(100) NOT NULL default '',
  bid_sub_desc varchar(100) default '',
  bid_sub_amount varchar(20) NULL default '',
  bid_winner tinyint(1) NOT NULL default '0',
  bid_date TIMESTAMP,
  PRIMARY KEY  (bid_id),
  UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name)
 ) TYPE=MyISAM;


 and I am trying toload a file with the following data:
 ,Construction Management,RCG Consulting,,Orly-Belle
 ,Construction Management,RCG Consulting,,American Engineers

 I am using phpmyadmin and the date always gets enter as 00

 What do I need to do to force the current time stamp?
 I am using mysql version 4.0.21-standard

Don't specify the bid_date as an empty string - ie take the last ,
from the end. The empty string is being converted to '00' because
it's an invalid date.

If you don't specify that column, it will default to 'now()'.

mysql insert into bid(bid_proj_name) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql insert into bid(bid_proj_name, bid_date) values ('test 2', '');
Query OK, 1 row affected (0.01 sec)

mysql select bid_id, bid_proj_name, bid_date from bid;
++---++
| bid_id | bid_proj_name | bid_date   |
++---++
|  1 | test  | 20060706110203 |
|  2 | test 2| 00 |
++---++
2 rows in set (0.00 sec)



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

automatic timestamp

[Nestor]

People,

I have the following table:
DROP TABLE IF EXISTS bid;
CREATE TABLE bid (
 bid_id int(11) NOT NULL auto_increment,
 bid_proj_name varchar(100) NOT NULL default '',
 bid_prop_name varchar(100) NOT NULL default '',
 bid_amount varchar(20) NULL default '',
 bid_sub_name varchar(100) NOT NULL default '',
 bid_sub_desc varchar(100) default '',
 bid_sub_amount varchar(20) NULL default '',
 bid_winner tinyint(1) NOT NULL default '0',
 bid_date TIMESTAMP,
 PRIMARY KEY  (bid_id),
 UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name)
) TYPE=MyISAM;


and I am trying toload a file with the following data:
,Construction Management,RCG Consulting,,Orly-Belle
,Construction Management,RCG Consulting,,American Engineers

I am using phpmyadmin and the date always gets enter as 00

What do I need to do to force the current time stamp?
I am using mysql version 4.0.21-standard

Thanks,

Néstor :-)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: automatic timestamp

[Scott Haneda]

 People,
 
 I have the following table:
 DROP TABLE IF EXISTS bid;
 CREATE TABLE bid (
   bid_id int(11) NOT NULL auto_increment,
   bid_proj_name varchar(100) NOT NULL default '',
   bid_prop_name varchar(100) NOT NULL default '',
   bid_amount varchar(20) NULL default '',
   bid_sub_name varchar(100) NOT NULL default '',
   bid_sub_desc varchar(100) default '',
   bid_sub_amount varchar(20) NULL default '',
   bid_winner tinyint(1) NOT NULL default '0',
   bid_date TIMESTAMP,
   PRIMARY KEY  (bid_id),
   UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name)
 ) TYPE=MyISAM;
 
 
 and I am trying toload a file with the following data:
 ,Construction Management,RCG Consulting,,Orly-Belle
 ,Construction Management,RCG Consulting,,American Engineers
 
 I am using phpmyadmin and the date always gets enter as 00
 
 What do I need to do to force the current time stamp?
 I am using mysql version 4.0.21-standard

IIRC, you need two timestamp fields for auto to work, default timestamps for
the first timestamp column will be ignored, but the first column will get
auto timestamp in a table that has more than one timestamp column.

I usually set up two, updated and added, and I always set the added field to
NOW()

It explains it pretty well in the docs here
http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: automatic timestamp

[Chris]

Nestor wrote:

People,

I have the following table:
DROP TABLE IF EXISTS bid;
CREATE TABLE bid (
 bid_id int(11) NOT NULL auto_increment,
 bid_proj_name varchar(100) NOT NULL default '',
 bid_prop_name varchar(100) NOT NULL default '',
 bid_amount varchar(20) NULL default '',
 bid_sub_name varchar(100) NOT NULL default '',
 bid_sub_desc varchar(100) default '',
 bid_sub_amount varchar(20) NULL default '',
 bid_winner tinyint(1) NOT NULL default '0',
 bid_date TIMESTAMP,
 PRIMARY KEY  (bid_id),
 UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name)
) TYPE=MyISAM;


and I am trying toload a file with the following data:
,Construction Management,RCG Consulting,,Orly-Belle
,Construction Management,RCG Consulting,,American Engineers

I am using phpmyadmin and the date always gets enter as 00

What do I need to do to force the current time stamp?
I am using mysql version 4.0.21-standard


Don't specify the bid_date as an empty string - ie take the last , 
from the end. The empty string is being converted to '00' because 
it's an invalid date.


If you don't specify that column, it will default to 'now()'.

mysql insert into bid(bid_proj_name) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql insert into bid(bid_proj_name, bid_date) values ('test 2', '');
Query OK, 1 row affected (0.01 sec)

mysql select bid_id, bid_proj_name, bid_date from bid;
++---++
| bid_id | bid_proj_name | bid_date   |
++---++
|  1 | test  | 20060706110203 |
|  2 | test 2| 00 |
++---++
2 rows in set (0.00 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

update problem with timestamp columns

[Danish]

Hi,
 
 I have a table with a time stamp column defined as

  ts timestamp(14) NOT NULL

this is the first timestamp value in a series of timestamp columns.
Whenever I update a row in the table ts gets updated with the current
timestamp. I read from the documentaion that the first timestamp column
in a create statement with neither DEFAULT nor ON UPDATE clauses is the
same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

 To solve the problem I try to alter the table with the following
command:

ALTER TABLE `test` CHANGE `ts` `ts` TIMESTAMP( 14 ) NOT NULL DEFAULT
'CURRENT_TIMESTAMP()'

but whenever I update a row after running the command above ts still
gets updated.

 Any ideas how I can restrict mysql to not update the ts value on
update.

BTW, Iam testing this on 3.23 but it has the same effect on 4.1 as well
Regards,
Danish


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Fwd: update problem with timestamp columns

[sheeri kritzer]

Did you successfully alter the table?  What does SHOW CREATE TABLE give you?


mysql CREATE TABLE test ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
PRIMARY KEY, ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ts_modify
TIMESTAMP );
Query OK, 0 rows affected (0.00 sec)

mysql insert into test(id) VALUES();insert into test(id)
VALUES();insert into test(id) VALUES();insert into test(id) VALUES();
Query OK, 1 row affected, 1 warning (0.00 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)

(the warnings are that the '' for id was truncated and the
auto_increment was used)

mysql select * from test;
++-+-+
| id | ts_create   | ts_modify   |
++-+-+
|  1 | 2006-05-19 11:18:07 | -00-00 00:00:00 |
|  2 | 2006-05-19 11:18:24 | -00-00 00:00:00 |
|  3 | 2006-05-19 11:18:25 | -00-00 00:00:00 |
|  4 | 2006-05-19 11:18:25 | -00-00 00:00:00 |
++-+-+
4 rows in set (0.00 sec)

and then I update to see if it changes the timestamp at ts_create:

mysql update test set ts_modify=DATE_SUB(NOW(),INTERVAL 6 MONTH);
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql select * from test;
++-+-+
| id | ts_create   | ts_modify   |
++-+-+
|  1 | 2006-05-19 11:18:07 | 2005-11-19 11:18:59 |
|  2 | 2006-05-19 11:18:24 | 2005-11-19 11:18:59 |
|  3 | 2006-05-19 11:18:25 | 2005-11-19 11:18:59 |
|  4 | 2006-05-19 11:18:25 | 2005-11-19 11:18:59 |
++-+-+
4 rows in set (0.00 sec)

So you see, it worked -- it did not update the ts_create timestamp.  I
would guess your alter table failed.

-Sheeri

On 5/18/06, Danish [EMAIL PROTECTED] wrote:

Hi,

 I have a table with a time stamp column defined as

  ts timestamp(14) NOT NULL

this is the first timestamp value in a series of timestamp columns.
Whenever I update a row in the table ts gets updated with the current
timestamp. I read from the documentaion that the first timestamp column
in a create statement with neither DEFAULT nor ON UPDATE clauses is the
same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

 To solve the problem I try to alter the table with the following
command:

ALTER TABLE `test` CHANGE `ts` `ts` TIMESTAMP( 14 ) NOT NULL DEFAULT
'CURRENT_TIMESTAMP()'

but whenever I update a row after running the command above ts still
gets updated.

 Any ideas how I can restrict mysql to not update the ts value on
update.

BTW, Iam testing this on 3.23 but it has the same effect on 4.1 as well
Regards,
Danish


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

I Have Problem with this Timestamp

[Melvin Zamora]

Hi MySql Community,

I put up a default null value TIMESTAMP on field type TIMESTAMP. Now I enter to 
the database as '-00-00 00:00:00' default for null value. From this on, I 
mapped it using hibernate with mysql-connector-java-3.1.6-bin.jar driver. I 
extract it but error occurred:

Caused by: java.sql.SQLException: Cannot convert value '-00-00 00:00:00' 
from column 9 to TIMESTAMP.

What did I done wrong? Is there a problem with the driver? Or should I use 
DATETIME instead? Some says the format is supposed to be  '-00-00 00.00.00' 
instead, if so this would be a database problem, is this a some kind of 
unresovled bug? Lastly, any remedy for the meantime for this?

Thanks a lot.
Melvin R. Zamora



-
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates 
starting at 1cent;/min.

timestamp not null

[[EMAIL PROTECTED]]

Hi!

I created a table and, into it, a timestamp field:
... EXPIRES TIMESTAMP NOT NULL, ...

When I issue the command describe it shows the field expires allows nulls 
and defaults to CURRENT_TIMESTAMP. Also, each time I update 
a field other than expires in this table, expires gets updated to the 
current timestamp.

Does anybody know how can I make a timestamp field be not null?
Lots of thanks to you all.


Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timestamp not null

[Martijn Tonies]

Hi,

 I created a table and, into it, a timestamp field:
 ... EXPIRES TIMESTAMP NOT NULL, ...

 When I issue the command describe it shows the field expires allows
nulls and defaults to CURRENT_TIMESTAMP. Also, each time I update
 a field other than expires in this table, expires gets updated to the
current timestamp.

 Does anybody know how can I make a timestamp field be not null?
 Lots of thanks to you all.

If you want to store date/time values, do not use the TIMESTAMP
datatype.

What is it that you're trying to do?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]