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. - 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
-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
Re: Timestamp and the On Update Current_Timestamp clause
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
Re: Timestamp and the On Update Current_Timestamp clause
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
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]
Re: timestamp for update and insert
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
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
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
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
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
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
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
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: timestamp not null
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]
Re: timestamp not null
I need to create a commands table. A program will periodically check on this table whether there's a pending command for it to execute or not. Whatever the reason, this program might read a command but not acknowledge it's execution. Other program will check out whether the command timeout has expired or not and so act accordingly. I guess I can use some sort of integer in order to represent it as a unix timestamp, but I would prefer to use a timestamp. Any suggestions? Kind regards Mensaje original De: [EMAIL PROTECTED] Recibido: 10/04/2006 11:51 Para: [EMAIL PROTECTED], mysql@lists.mysql.com Asunto: Re: timestamp amp; not null 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 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
I need to create a commands table. A program will periodically check on this table whether there's a pending command for it to execute or not. Whatever the reason, this program might read a command but not acknowledge it's execution. Other program will check out whether the command timeout has expired or not and so act accordingly. I guess I can use some sort of integer in order to represent it as a unix timestamp, but I would prefer to use a timestamp. As I said -- to store date/time values, you should NOT use the TIMESTAMP datatype, cause it isn't supposed to be used to store custom date/time values. I suggest you read the documentation on MySQL Date/Time datatypes -- it has a fine explanation of what to use and what to use the TIMESTAMP datatype for. Don't bother going the Unix integer timestamp thingy route. Just read this: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html 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]
Re: TIMESTAMP field not automatically updating last_updated field
- Original Message - From: Ferindo Middleton Jr [EMAIL PROTECTED] To: Ferindo Middleton Jr [EMAIL PROTECTED] Cc: Hank [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, March 31, 2006 7:30 PM Subject: Re: TIMESTAMP field not automatically updating last_updated field Ferindo Middleton Jr wrote: Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo I'm running 5.0.19-nt. I haven't had a chance to test it but should it make any difference if I say: last_updated TIMESTAMP, than if I say all this: last_updatedTIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, I think this may be the difference in why some tables are auto incrementing and others aren't. Ferindo I'm using 4.1.11 on Solaris. I've explicitly created a table with one timestamp field and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL I haven't tried an explicit update (useless to me) but an insert...on duplicate key update does not update the timestamp field. Have you, Ferindo, had any success yet? Does anyone have any further thoughts? (I just realized I need this also) --Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. It's true. No Update = No change of timestamp! -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
Ferindo Middleton Jr wrote: Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo I'm running 5.0.19-nt. I haven't had a chance to test it but should it make any difference if I say: last_updated TIMESTAMP, than if I say all this: last_updatedTIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, I think this may be the difference in why some tables are auto incrementing and others aren't. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
I think I've seen this complaint posted before but I ignored but now I realize that in some of my db tables' last_updated field the value is automatically updating on UPDATEs to records while in other tables the last_updated fields for some strange reason aren't automatically updating. I'll usually use the following line in my table declarations: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, In some tables it automatically updates on subsequent updates to the table and in others it will not. The purpose here is to have the last_updated field automatically append to the current timestamp... the application on the front end doesn't specify the time to MySQL but rather expects that it's always going to be UPDATEd to the current time slot. What am I doing wrong what command should I issue to my tables to correct it? Thanks What veriosn of mysql, timestamp handling has changed from one version to the next? Perhaps you have others in your table, I think only the first is updated, at least, in pre 4.1 days, after that, check the docs for the correct behavior. -- - 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: TIMESTAMP field not automatically updating last_updated field
are you having two timestamp fields in a table (ie a created and a last_updated)? -j On Mar 30, 2006, at 5:17 PM, Ferindo Middleton Jr wrote: I think I've seen this complaint posted before but I ignored but now I realize that in some of my db tables' last_updated field the value is automatically updating on UPDATEs to records while in other tables the last_updated fields for some strange reason aren't automatically updating. I'll usually use the following line in my table declarations: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, In some tables it automatically updates on subsequent updates to the table and in others it will not. The purpose here is to have the last_updated field automatically append to the current timestamp... the application on the front end doesn't specify the time to MySQL but rather expects that it's always going to be UPDATEd to the current time slot. What am I doing wrong what command should I issue to my tables to correct it? Thanks Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
jonathan wrote: are you having two timestamp fields in a table (ie a created and a last_updated)? -j On Mar 30, 2006, at 5:17 PM, Ferindo Middleton Jr wrote: I think I've seen this complaint posted before but I ignored but now I realize that in some of my db tables' last_updated field the value is automatically updating on UPDATEs to records while in other tables the last_updated fields for some strange reason aren't automatically updating. I'll usually use the following line in my table declarations: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, In some tables it automatically updates on subsequent updates to the table and in others it will not. The purpose here is to have the last_updated field automatically append to the current timestamp... the application on the front end doesn't specify the time to MySQL but rather expects that it's always going to be UPDATEd to the current time slot. What am I doing wrong what command should I issue to my tables to correct it? Thanks Ferindo --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No just the one timestamp field (last_updated) which I expect to be given a timestamp on the initial INSERT and then continue to be automatically updated to the current time on subsequent UPDATEs to any given row... Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Timestamp problem in mysql5.0.18
See, for the UK, on that date, 2am to 2:59 inclusive do not officially
exist - hence, 3am to 3:59 for GMT+1, etc. I couldn't quite figure why 3am
was being rejected until now. I'm impressed that MySQL knows that {:v)
--
Jason Teagle
[EMAIL PROTECTED]
-Original Message-
From: Ricardas.S [mailto:[EMAIL PROTECTED]
Sent: 21 March 2006 14:31
To: [EMAIL PROTECTED]
Subject: Re: Timestamp problem in mysql5.0.18
Yes, I think you are right, it should be the main reason of
insert failure.
Thank you for good idea.
Ricka
- Original Message -
From: Jason Teagle [EMAIL PROTECTED]
To: Ricardas.S [EMAIL PROTECTED]
Sent: Tuesday, March 21, 2006 14:55
Subject: RE: Timestamp problem in mysql5.0.18
All other date or hour values I tried, works good, but this one
is not accepted. UPDATE statement behaves the same.
Server time zone is GMT+2.
I tried 5.0.18nt and two linux versions, result is the same.
I tried to change time zone, and noticed that mysql server does
not accept 2006-03-26 date with hour values which are equals GMT
offset + 1.
When I tried with ALLOW_INVALID_DATES option enabled, then server
simply changes hour upward to 04.
Is it mysql bug?
I believe that the clocks go forward in the UK (and possibly other
countries) on the 26th - 2am suddenly becomes 3am. I wonder if
this is part
of the problem? Seems a bit of a coincidence, especially as you
say it went
to 4am.
--
Jason Teagle
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
Further, your reporting will skew the data -- let's say once a day you want to know the last time that row was read. Well, the first day, you'll get accurate numbers. The second day, though, you'll end up seeing that each row was read at latest the day before, because you read it searching for the date. That's an UPDATE you really want to build into your APPLICATION layer. It's a BAD idea to store business logic/rules in the data layer. -Sheeri On 3/13/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: fbsd_user [EMAIL PROTECTED] wrote on 03/13/2006 01:10:17 PM: In my mysql 4.4 table definition the default attributes are (ON UPDATE CURRENT_TIMESTAMP). Reading the manual my understanding is this is saying that the auto timestamp update feature is active. The manual does not say what the trigger is to make the timestamp in the row to be bumped to the current timestamp. I have noticed that the timestamp field is only bumped when I update a field in the row. I would like it to be auto bumped every time the row is selected/read. Is there a way to do this without creating a timestamp from date in my php code and then updating the row instead of reading the row? The final goal is to auto bump the timestamp every time the user logs on. It sounds like there is a logon process that the user must perform. What's the issue you have about updating your timestamp from within that process? That's where it sounds most logical to me to add the code to bump your timestamp value. From a design point of view, it would seriously slow down the entire server if it had to check for something to do on EVERY read from ANY table. In order to do what you want the database to do, that facility would need to exist so that the engine could bump the timestamp automatically. It is generally much better to NOT write SELECT statements to a log or to do anything else that would slow them down. Checking every row that ever got selected to see if any column in it is an auto-update-on-select-timestamp column would do that in a major way. It also breaks all kinds of SQL rules to make a SELECT clause into something that modifies data. That would lead to all sorts of data chaos and I want no part of it. I am sorry, but if you want the timestamp changed you are going to have to initiate that change by an UPDATE statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
fbsd_user [EMAIL PROTECTED] wrote on 03/13/2006 01:10:17 PM: In my mysql 4.4 table definition the default attributes are (ON UPDATE CURRENT_TIMESTAMP). Reading the manual my understanding is this is saying that the auto timestamp update feature is active. The manual does not say what the trigger is to make the timestamp in the row to be bumped to the current timestamp. I have noticed that the timestamp field is only bumped when I update a field in the row. I would like it to be auto bumped every time the row is selected/read. Is there a way to do this without creating a timestamp from date in my php code and then updating the row instead of reading the row? The final goal is to auto bump the timestamp every time the user logs on. It sounds like there is a logon process that the user must perform. What's the issue you have about updating your timestamp from within that process? That's where it sounds most logical to me to add the code to bump your timestamp value. From a design point of view, it would seriously slow down the entire server if it had to check for something to do on EVERY read from ANY table. In order to do what you want the database to do, that facility would need to exist so that the engine could bump the timestamp automatically. It is generally much better to NOT write SELECT statements to a log or to do anything else that would slow them down. Checking every row that ever got selected to see if any column in it is an auto-update-on-select-timestamp column would do that in a major way. It also breaks all kinds of SQL rules to make a SELECT clause into something that modifies data. That would lead to all sorts of data chaos and I want no part of it. I am sorry, but if you want the timestamp changed you are going to have to initiate that change by an UPDATE statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: TimeStamp issue
rtroiana wrote: Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. Why not? It is not 2037 yet. Timestamp is designed to record when records are updated, not for storing arbitrary dates and times. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeStamp issue
If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issue Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeStamp issue
In fact, no time values in MySQL are fractional (yet). All times are stored to the nearest second regardless of which date-time-like storage type you use. They way Rhino phrased his answer, it sounded as though TIMSTAMP would save fractional seconds. It doesn't. He is spot on about needing a separate column to store any values that represent fractions of seconds. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 03/02/2006 01:25:36 PM: If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issue Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeStamp issue
Thanks for keeping me honest! I'd forgotten that MySQL timestamps don't keep the fractional parts of seconds either; I mostly use DB2 which keeps the fractional parts (microseconds) and forgot about this quirk of MySQL. -- Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino Cc: mysql@lists.mysql.com ; rtroiana Sent: Thursday, March 02, 2006 1:42 PM Subject: Re: TimeStamp issue In fact, no time values in MySQL are fractional (yet). All times are stored to the nearest second regardless of which date-time-like storage type you use. They way Rhino phrased his answer, it sounded as though TIMSTAMP would save fractional seconds. It doesn't. He is spot on about needing a separate column to store any values that represent fractions of seconds. Shawn GreenDatabase AdministratorUnimin Corporation - Spruce Pine "Rhino" [EMAIL PROTECTED] wrote on 03/02/2006 01:25:36 PM: If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: "rtroiana" [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issueHi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that "TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0." Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use "CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- 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.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: TimeStamp issue
Thanks to all of you for replying. I'm using DATETIME instead of TIMESTAMP now. Although I still haven't find the answer for my second question. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? _ From: Rhino [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 3:50 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; rtroiana Subject: Re: TimeStamp issue Thanks for keeping me honest! I'd forgotten that MySQL timestamps don't keep the fractional parts of seconds either; I mostly use DB2 which keeps the fractional parts (microseconds) and forgot about this quirk of MySQL. -- Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino mailto:[EMAIL PROTECTED] Cc: mysql@lists.mysql.com ; rtroiana mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 1:42 PM Subject: Re: TimeStamp issue In fact, no time values in MySQL are fractional (yet). All times are stored to the nearest second regardless of which date-time-like storage type you use. They way Rhino phrased his answer, it sounded as though TIMSTAMP would save fractional seconds. It doesn't. He is spot on about needing a separate column to store any values that represent fractions of seconds. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 03/02/2006 01:25:36 PM: If you need a broader range of dates, you could use DATETIME instead of TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through '-12-31 23:59:59'. The only big difference is that DATETIME does not store the fractional part of the seconds, e.g. milliseconds/microseconds/nanonseconds. If you have to keep the fractional part of the seconds, you could store them in a second column defined as some kind of integer. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 02, 2006 10:10 AM Subject: TimeStamp issue Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006 -- 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.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006
Re: TimeStamp issue
On 3/2/06, rtroiana [EMAIL PROTECTED] wrote: Thanks to all of you for replying. I'm using DATETIME instead of TIMESTAMP now. Although I still haven't find the answer for my second question. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Sure. You could run a trigger on an insert statement to update the DATETIME field to the contents of SELECT NOW(); -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp error
pedro mpa wrote: Greetings! I am building a website using MySQL 5.0.18 and PHP 5.1.2. When I try to insert in a table a timestamp value from php's mktime() I get the following error: 1292: Incorrect datetime value: '1139776424' for column 'access_date' at row 1 The sql for the table is: CREATE TABLE `members_acs` ( `id` int(17) unsigned NOT NULL auto_increment, `member_id ` int(13) unsigned default NULL, `access_date` timestamp NOT NULL default '-00-00 00:00:00', `ip` varchar(15) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Are timestamps different from php to mysql? When I first create a timestamp field it defaults to CURRENT_TIMESTAMP then the following will default to -00-00 00:00:00 . Is this the normal behaviour? What am I doing wrong? Thanks in advance. Pedr. The mysql TIMESTAMP is not a unix timestamp, so it isn't compatible with the output of php's mktime(). See the manual for the details of the TIMESTAMP type: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html http://dev.mysql.com/doc/refman/5.0/en/datetime.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
Hello. Have you checked if the timestamp column is set with CURRENT_TIMESTAMP as the default value? If it doesnt, you can try this: ALTER TABLE tbl MODIFY column TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; And mysql will fill the column with current timestamp when a new row is appended or when a old one is updated. It seems that MySQL timestamp have changed a lot in 4.1 version. Good luck! Richard AB - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, December 18, 2005 5:03 PM Subject: timestamp hi! I'm using mysql 4.1 and I have a problem with a TIMESTAMP column (with all of them actually). I've used them before in mysql 3.x and they worked just fine. And at first I thought 'they changed something', but in the mysql 4.1 manual it still says A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. The first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value. But when I try to insert a row into a simple table like this insert into test set txt='something', the field data, which is of type TIMESTAMP, doesn't initiate with the current date, but turns out to be '-00-00 00:00:00'. I've also tried with update, and with other tables too. The same thing. Could anyone please help? Thanks. -- 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.1.371 / Virus Database: 267.14.1/204 - Release Date: 15/12/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.1/204 - Release Date: 15/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
Hi Ryan, On Wed, 2005-11-16 at 19:16 +0800, Ryan Escarez wrote: is it possible to get the the given (unix)timestamp in milliseconds since the epoch? No, it is not possible. However this is on the roadmap. This is also reported as Bug #8523: http://bugs.mysql.com/bug.php?id=8523 Regards, Petr -- Petr Chardin, Software Developer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
Ryan,
Try to use this date functions.
* DATE_ADD(date,INTERVAL expr type) , DATE_SUB(date,INTERVAL expr type)
These functions perform date arithmetic. date is a DATETIME or DATE value
specifying the starting date. expr is an expression specifying the interval
value to be added or subtracted from the starting date. expr is a string;
it may start with a '-' for negative intervals. type is a keyword
indicating how the expression should be interpreted.
The INTERVAL keyword and the type specifier are not case sensitive.
The following table shows how the type and expr arguments are related:
type Value Expected expr Format MICROSECOND MICROSECONDS SECOND SECONDS
MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS
YEAR YEARS SECOND_MICROSECOND 'SECONDS.MICROSECONDS' MINUTE_MICROSECOND
'MINUTES.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' HOUR_MICROSECOND
'HOURS.MICROSECONDS' HOUR_SECOND 'HOURS:MINUTES:SECONDS' HOUR_MINUTE
'HOURS:MINUTES' DAY_MICROSECOND 'DAYS.MICROSECONDS' DAY_SECOND 'DAYS
HOURS:MINUTES:SECONDS' DAY_MINUTE 'DAYS HOURS:MINUTES' DAY_HOUR 'DAYS
HOURS' YEAR_MONTH 'YEARS-MONTHS'
The type values DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, and MICROSECOND are allowed as of MySQL 4.1.1. The
values QUARTER and WEEK are allowed as of MySQL 5.0.0.
MySQL allows any punctuation delimiter in the expr format. Those shown
in the table are the suggested delimiters. If the date argument is a DATE
value and your calculations involve only YEAR, MONTH, and DAY parts (that
is, no time parts), the result is a DATE value. Otherwise, the result is a
DATETIME value.
As of MySQL 3.23, INTERVAL expr type is allowed on either side of the +
operator if the expression on the other side is a date or datetime value.
For the - operator, INTERVAL expr type is allowed only on the right side,
because it makes no sense to subtract a date or datetime value from an
interval. (See examples below.)
mysql SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
- '1998-01-01 00:00:00'
mysql SELECT INTERVAL 1 DAY + '1997-12-31';
- '1998-01-01'
mysql SELECT '1998-01-01' - INTERVAL 1 SECOND;
- '1997-12-31 23:59:59'
mysql SELECT DATE_ADD('1997-12-31 23:59:59',
- INTERVAL 1 SECOND);
- '1998-01-01 00:00:00'
mysql SELECT DATE_ADD('1997-12-31 23:59:59',
- INTERVAL 1 DAY);
- '1998-01-01 23:59:59'
mysql SELECT DATE_ADD('1997-12-31 23:59:59',
- INTERVAL '1:1' MINUTE_SECOND);
- '1998-01-01 00:01:00'
mysql SELECT DATE_SUB('1998-01-01 00:00:00',
- INTERVAL '1 1:1:1' DAY_SECOND);
- '1997-12-30 22:58:59'
mysql SELECT DATE_ADD('1998-01-01 00:00:00',
- INTERVAL '-1 10' DAY_HOUR);
- '1997-12-30 14:00:00'
mysql SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
- '1997-12-02'
mysql SELECT DATE_ADD('1992-12-31 23:59:59.02',
-INTERVAL '1.99' SECOND_MICROSECOND);
- '1993-01-01 00:00:01.01'
If you specify an interval value that is too short (does not include
all the interval parts that would be expected from the type keyword), MySQL
assumes that you have left out the leftmost parts of the interval value.
For example, if you specify a type of DAY_SECOND, the value of expr is
expected to have days, hours, minutes, and seconds parts. If you specify a
value like '1:10', MySQL assumes that the days and hours parts are missing
and the value represents minutes and seconds. In other words, '1:10'
DAY_SECOND is interpreted in such a way that it is equivalent to '1:10'
MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME
values as representing elapsed time rather than as time of day.
If you add to or subtract from a date value something that contains a
time part, the result is automatically converted to a datetime value:
mysql SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
- '1999-01-02'
mysql SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
- '1999-01-01 01:00:00'
If you use really malformed dates, the result is NULL. If you add
MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger
than the maximum day for the new month, the day is adjusted to the maximum
days in the new month:
mysql SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
- '1998-02-28'
Thank you,
At 07:47 PM 11/16/2005, Petr Chardin wrote:
http://bugs.mysql.com/bug.php?id=8523
Ehrwin C. Mina
9/F Tower 2
RCBC Plaza
6819 Ayala Avenue cor. Sen. Gil J. Puyat Avenue
Makati City 1200 Philippines
Cell (63 918) 930 4383
Tel/Fax(63 2) 757 2633
Email [EMAIL PROTECTED]
Webwww.chikka.com
This message and any attachment are confidential and may be privileged
Re: timestamp and php
2005-08-30 13:50.05 this is the text content (i) sort the returned rows in order (latest first) http://dev.mysql.com/doc/mysql/en/sorting-rows.html (ii) be able to extract the individual parts of the date and display them in UK format (ddmm) http://www.php.net/manual/en/function.strtotime.php http://www.php.net/manual/en/function.date.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp and php
[EMAIL PROTECTED] wrote: Hi, I have a row in mysql database called time and is just a simple timestamp column When I echo it out echo $row['time']; echo $row['content']; I get the following 2005-08-30 13:50.05 this is the text content Now I am not worried about the time but I would like to know how to (i) sort the returned rows in order (latest first) (ii) be able to extract the individual parts of the date and display them in UK format (ddmm) 30.08.2005 this is the text from 30th of August 27.08.2005 this is the text from 27th of August 27.08.2005 this is the text from 23rd of August thanks, R. Use ORDER BY to get sorted results http://dev.mysql.com/doc/mysql/en/sorting-rows.html. Use DATE_FORMAT() to get the date output you want http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html. SELECT DATE_FORMAT(time, '%e.%c.%Y') AS time, content FROM your_table WHERE where conditions as needed ORDER BY time DESC; The '%e.%c.%Y' will give 3.1.2005 for the 3rd of January. Use '%d.%m.%Y' to get 03.01.2005 instead. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Timestamp problem.
If you run the select SELECT NOW() + 1*RAND(); a few times, you'll see that not all values are valid timestamps, e.g.: mysql SELECT NOW() + 1*RAND(); +--+ | NOW() + 1*RAND() | +--+ | 20050713112881 | +--+ 1 row in set (0.00 sec) If you're trying to add seconds onto a timestamp, you're probably looking for something like: SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()) + 1*RAND()); -Original Message- From: Antonio Gulli [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 13, 2005 8:31 AM To: mysql@lists.mysql.com Subject: Timestamp problem. I have the following part in a schema describe feeds . | pubdate | timestamp| YES | | CURRENT_TIMESTAMP | | show create table feeds; `pubdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, when i make an INSERT INTO feeds(, pubdate) VALUES (,NOW()+1*RAND()) is there any chance to have: mysql select count(*) from feeds where pubdate = 0; +--+ | count(*) | +--+ | 593923 | +--+ 1 row in set (5.90 sec) mysql select count(*) from feeds where pubdate 0; +--+ | count(*) | +--+ | 287532 | +--+ 1 row in set (5.33 sec) -- Sometimes life hits you in the head with a brick. Don't lose faith. -- 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 problem
Hello. There were several bugs related to timestamp at 4.0.22 and later. Does the problem remains on 4.1.12 (4.0.24)? manasvini nandakumar [EMAIL PROTECTED] wrote: Hi all, I have a very strange problem with mysql-4.0.22 running on a big endian processor platform.When the timestamp gets updated as '2005-01-01 23:00:00', it actually seems to be converted internally as 2005010423.Similarly '2005-01-02 23:00:00' is '2005010523'. The problem is that let us say ,some value in one of the rows got updated at a system time of '2005-01-02 03:00:00'.It's timestamp is now '2005010203'.But the DB refresh function is unable to get hold of this row in the query SELECT * from tbl_name WHERE timestamp '2005010123'. This problem happens only when the hour field is 23 on a Big endian system.(The day somehow gets incremented by 3 during comparison and insertion).It works fine on my PC. I am hoping someone could give me some ideas as to why this may happen Thanks in advance, Manasvini __ Discover Yahoo! Use Yahoo! to plan a weekend, have fun online and more. Check it out! http://discover.yahoo.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)
On 6/9/05, Martijn Tonies wrote: http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html Absolutely brilliant document *g* ... So now, it makes a difference if it's the first TIMESTAMP column, if it's running in MaxDB mode, if it has a defaulf of NULL (which will be silently changed), if it has no default, a default of CURRENT_TIMESTAMP, or it matters if there's an ON UPDATE clause... And it depends on which *minor* release it is. 4.1.0 is different from 4.1.1 is different from 4.1.2 is different from 4.1.3 is different from 4.1.6. And it depends on how long ago you sacrificed a goat and the position og te moon. Damn man... The guy who thought this up should be smacked in the head! Should be made to fix it :) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp and it's usage (Re: Seriously.. When are we going to get subqueries?!)
http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html Absolutely brilliant document *g* ... So now, it makes a difference if it's the first TIMESTAMP column, if it's running in MaxDB mode, if it has a defaulf of NULL (which will be silently changed), if it has no default, a default of CURRENT_TIMESTAMP, or it matters if there's an ON UPDATE clause... And it depends on which *minor* release it is. 4.1.0 is different from 4.1.1 is different from 4.1.2 is different from 4.1.3 is different from 4.1.6. And it depends on how long ago you sacrificed a goat and the position og te moon. Changing functionality and adding columns etc in minor point releases is something I really hate about MySQL. Same for removing or changing column names from system commands. Truely annoying. Never heard of backwards compatibility I guess... Had a conversation with some MySQL folks - really hard to get the point through to them... Damn man... The guy who thought this up should be smacked in the head! Should be made to fix it :) No, smacked in the head... :-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp and DST: impossible to backup database? *AND* bugs in TIMEDIFF, FROM_UNIXTIME, et.al.?
Thank you Michael for your very thoughtful reply. I know that it takes time and effort to answer at the level you did. Michael Stassen Michael.Stassen-at-verizon.net |Lists| wrote: You seem to have a fundamental misunderstanding of the TIMESTAMP type. No timezone or DST information is stored in a TIMESTAMP column. Yup. I thought it could be used to unambiguously represent any and all points in time. It can't. Thats it in a nutshell. I need to be able to sort, get and set the time unambiguously, also during the one problem hour in october. I need to know that if I put in a field with a time value I can reliably retrieve it again. And that if a record went in at time X and another in at time Y, Y-X is accurate for all values of Y and X, regardless of how we humans have decided to present X and Y to each other. (Standard computer stuff, no?) DATETIME is ambiguous, seconds since epoch UTC is not. Maybe my surprise is more: Hey, depending on now(), a UNIX_TIMESTAMP(2004-10-31 02:15:00) has two different interal values!!! (Why now() should have any effect on that is still weird to me... I realize *how* it ends up having an effect implementationally, but it *shouldn't*.) The other value is not representable at all by any DATETIME value. And sorting on a DATETIME gives one result now and another after a dump/restore cycle. And there is no way around that. We'll change our application to int(32) unsigned and handle presentation client-side. I don't think I'll ever use a DATETIME again... Maybe thats just me. Thanks again, Michael. Peter -- Peter Valdemar Mørch http://www.morch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp and DST: impossible to backup database? *AND* bugs in TIMEDIFF, FROM_UNIXTIME, et.al.?
Peter Valdemar Mørch wrote: From the lack of responses I take it that nobody disagrees that the handling of the timestamp type is fundamentally broken in every version of MySQL. Silence does not necessarily indicate assent. You asked a complicated question (when the U.S. list members were on holiday rather than reading e-mail, I might add). I agree that it does not do what you expect, but the question of fundamentally broken is not so simple. I'll go ahead and file several bugs, and start changing our code to avoid the timestamp type altogther. I'm quite surprised! Peter Peter Valdemar Mørch swp5jhu02-at-sneakemail.com |Lists| wrote: *** Claim *** I seem to have discovered that MySQL cannot handle the hour where DST becomes non-DST reliably (on Oct 31st in CET): 2am 3am | problem | | time| --DST-/ / |/ / / /- non-DST -- Essentially, the problem seems to be that October 31, 2:15 am is ambiguous and MySQL cannot disambiguate between them. Well, of course. October 31, 2:15 am *is* ambiguous. Which one does it mean? You cannot tell from the value October 31, 2:15 am whether it is the first or second occurrence of that time. No system could -- additional info is required. It seems that internally MySQL stores timestamp values with all timezone and DST/non-DST information intact. But all operations (e.g. TIMEDIFF(), FROM_UNIXTIME() and UPDATE TABLE SET timestamp=timestamp+0) destroy this important information and operate only now()'s timezone ignoring the appropriate original source time zone. You seem to have a fundamental misunderstanding of the TIMESTAMP type. No timezone or DST information is stored in a TIMESTAMP column. TIMESTAMPs are stored internally as seconds since epoch UTC, according to the manual, but they are always translated to DATETIMES in the current timezone when retrieved. The operations you mention, TIMEDIFF(), FROM_UNIXTIME() and UPDATE TABLE SET timestamp=timestamp+0, all behave as expected, once you understand how they, and timestamps, work I just find it impossibly difficult to believe I'm the first one finding this, so now I'm curious. Googling has come up short. I very much hope I'm mistaken and that I don't have to change our code, database format and contents to store # secs since 1970 GMT in an int everywhere and avoid timestamps altogether because they don't work... That may be exactly what you should do, because that seems to be precisely what you've said you want. You want rows to be stamped with seconds since epoch so you can preserve order across the end of DST. Every operation you've attempted seems to be based on the assumption that timestamps are just seconds since epoch, but they aren't. Evidence for claim Essentially we have a log table using a timestamp column as a sort key. For our logic to work we need to be able to: * Sort reliably, because the order of entries is very important (we use the last log entry to determine current state). Entries that are made during 2am-3am DST *must* come before log entries that are made 2am-3am non-DST just as they follow chronologically in real life. Timestamps have 1 second resolution. Unless you are guaranteed never to get 2 log entries in the same second, you cannot use a timestamp to sort reliably. Don't you have an auto_increment primary key on which to sort? This works initially, but gets broken by a mysqldump db | mysql new_db cycle. Timestamp values in mysqldump output don't contain DST timezone information and hence of course it is lost during restore. There is no option to mysqldump to maintain this info in output. How does one backup a database then? (Other than cp or mysqlhotcopy which fails if host != localhost) Right, that's a problem, though I think not quite for the reason you describe. MySQL doesn't handle timezone as part of a datetime anywhere. Timezone is handled separately. Hence, we can't dump the timestamps' timezone/DST status on a row by row basis. The problem is that the internal seconds since epoch is converted to a datetime in the first place. Two different internal values translate to the same external datetime, so it's not a 1 to 1 function. Having lost information on output, you can't get it back on input. The question is whether or not this is a problem. That is, does this contradict the intended design of the timestamp column? * Be able to do TIMEDIFF() math and INSERT INTO TABLE ... VALUES(FROM_UNIXTIME(?)) maintaining the correct time. This is not possible. You are expecting functions to do what they aren't designed to do. TIMEDIFF() operates on times and datetimes, not on unix timestamps.
Re: timestamp and DST: impossible to backup database? *AND* bugs in TIMEDIFF, FROM_UNIXTIME, et.al.?
From the lack of responses I take it that nobody disagrees that the handling of the timestamp type is fundamentally broken in every version of MySQL. I'll go ahead and file several bugs, and start changing our code to avoid the timestamp type altogther. I'm quite surprised! Peter Peter Valdemar Mørch swp5jhu02-at-sneakemail.com |Lists| wrote: *** Claim *** I seem to have discovered that MySQL cannot handle the hour where DST becomes non-DST reliably (on Oct 31st in CET): 2am 3am | problem | | time| --DST-/ / |/ / / /- non-DST -- Essentially, the problem seems to be that October 31, 2:15 am is ambiguous and MySQL cannot disambiguate between them. It seems that internally MySQL stores timestamp values with all timezone and DST/non-DST information intact. But all operations (e.g. TIMEDIFF(), FROM_UNIXTIME() and UPDATE TABLE SET timestamp=timestamp+0) destroy this important information and operate only now()'s timezone ignoring the appropriate original source time zone. I just find it impossibly difficult to believe I'm the first one finding this, so now I'm curious. Googling has come up short. I very much hope I'm mistaken and that I don't have to change our code, database format and contents to store # secs since 1970 GMT in an int everywhere and avoid timestamps altogether because they don't work... Evidence for claim Essentially we have a log table using a timestamp column as a sort key. For our logic to work we need to be able to: * Sort reliably, because the order of entries is very important (we use the last log entry to determine current state). Entries that are made during 2am-3am DST *must* come before log entries that are made 2am-3am non-DST just as they follow chronologically in real life. This works initially, but gets broken by a mysqldump db | mysql new_db cycle. Timestamp values in mysqldump output don't contain DST timezone information and hence of course it is lost during restore. There is no option to mysqldump to maintain this info in output. How does one backup a database then? (Other than cp or mysqlhotcopy which fails if host != localhost) * Be able to do TIMEDIFF() math and INSERT INTO TABLE ... VALUES(FROM_UNIXTIME(?)) maintaining the correct time. This is not possible. It seems that for all MySQL functions, the 2am-3am period (DST/non-DST) chosen depends on now(), not the value of ?, yeilding off-by-an-hour errors. As illustrated by this short example (more elaborate examples follow below): susan:~# cat /etc/timezone Europe/Copenhagen susan:~# mysql -e ' select 1099185600-1099181400; select TIMEDIFF(FROM_UNIXTIME(1099185600), FROM_UNIXTIME(1099181400));' +---+ | 1099185600-1099181400 | +---+ | 4200 | +---+ ++ | TIMEDIFF(FROM_UNIXTIME(1099185600), FROM_UNIXTIME(1099181400)) | ++ | 00:10:00 | ++ But 4200 secs is 01:10:00, not 00:10:00!!! Before we: 1) File an enhancement request against mysqldump 2) File bugs against UNIX_TIMESTAMP() and TIMEDIFF() et. al. 3) change our database format and code to avoid the timestamp type altogether and use secondsSince1970 int instead (huge) , I'd love to hear that I'm mistaken and that these problems can be solved with current mysql code. I've tried 4.1.7 and 4.0.22. * More examples * I've created a table only with an ID and a timestamp. I've tried filling it in a loop in two ways (perl source code link below): 1) modifying system time and inserting into table using implied timestamp=now(). This works (order by timestamp - IDs are in order): ++-+---+ | ID | timestamp | unix_timestamp(timestamp) | ++-+---+ | 1 | 2004-10-31 01:45:00 |1099179900 | | 2 | 2004-10-31 02:15:00 |1099181700 | | 3 | 2004-10-31 02:45:00 |1099183500 | | 4 | 2004-10-31 02:15:00 |1099185300 | | 5 | 2004-10-31 02:45:00 |1099187100 | | 6 | 2004-10-31 03:15:00 |1099188900 | ++-+---+ But TIMEDIFF() on this otherwise correct data is erroneous: select TIMEDIFF(A.timestamp, B.timestamp) from table as A, table as B where A.ID=4 and B.ID=2;
Re: TIMESTAMP decimal year format available?
At 17:34 +0200 4/7/04, Pierre Didelon wrote: Hi, I am using mysql and I need to store timestamp. In input I got something like yyy-dddThh:mm:ss, ddd beeing the day of the year! I would like to make the minimun transformation before loading data in database, so I would like to enter the timestamp with a format handling decimal year yyy.ddd but AFA I understand the mysql doc, it seems not available? You understand correctly. Either transform the values before loading them, or load them into some other kind of column (CHAR?) and perform some kind of transformation using SQL to get them into the correct format. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp and alter table
I want to change the column choose to ENUM('y', 'n', 'weekly') without
changing the timestamp. How can I do that?
I tried ALTER TABLE as usual, and it didn't affect the timestamp. Sorry
I didn't try this from the beginning :)
--Batara
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp woes
Ryan A wrote: /** Somewhat new myself but I believe you can do something like SELECT (whatever you need) FROM tbl_users WHERE (UNIX_TIMESTAMP(now()) - ($days_last*24*60*60)) UNIX_TIMESTAMP(dat_and_tim). I will add the the PHP Cookbook and the MySQL Cookbook are godsends and that's where I got the solution. Grab a copy of these to help out - I've got a project due in a week and I'm using these non-stop. ***/ Hey, Thanks its working like a charm now to get the number of daysbut am still confused on how do I do the second comparision: selecting been sayyy 15th of Feb and 23rd of Feb.. any ideas? Thanks, -Ryan Again, I think you can convert each of the bookend dates to a unix-format time (time since epoch) in PHP (I assume that those dates aren't entered in the db) and then use that same UNIX_TIMESTAMP function on your table entries. Then pull entries out where the table values are between those two values. -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp woes
Scott Plumlee wrote: [EMAIL PROTECTED] wrote: Hi, (Please note: NEWBIE WARNING, below questions might sound stupid, but feel free to flame.) ;-) I have a table tbl_users with a field dat_an_time which is a timestamp(14). In that I have values such as: 2004022215 20040227042018 20040223015329 etc I have searched google/the manual for the answers to the below questions but I only found answers if the field is a datetime field and not a timestamp, I cannot change the format as I am the new developer of an old (already live) project and lots of scripts are already accessing this table. Please tell me how to do this with my current setup. Two questions: Question 1. In my php script I am taking the value of days_last which should run a select query to display all the records in the last $days_last days (for those of you who don't program in PHP/Perl $days_last is the variable that is a number which the user selects. eg: 10 or 5 or 35 etc) How do I format the query to get the results from my dat_an_time timestamp(14) field? SELECT * FROM tbl_users WHERE dat_an_time CUR_DATE() - INTERVAL $days_last DAY Depending on exactly what you want, you may need to change the to =. That is, if $last_days is 1, do you want today only (), or yesterday and today (=). Question 2. The client will be entering 2 fields in this format MMDD, how do I select * for all records that are between $T_field1 and $T_field2 from my dat_an_time timestamp(14) my records are like this: 2004022215 20040227042018 20040223015329 SELECT * FROM tbl_users WHERE data_an_time BETWEEN $T_field1 AND $T_field2 Any help, references to the manual or URLs will be appreciated. See http://www.mysql.com/doc/en/DATETIME.html and http://www.mysql.com/doc/en/Date_and_time_functions.html for details. Thanks in advance. Ryan. Somewhat new myself but I believe you can do something like SELECT (whatever you need) FROM tbl_users WHERE (UNIX_TIMESTAMP(now()) - ($days_last*24*60*60)) UNIX_TIMESTAMP(dat_and_tim). Note that UNIX_TIMESTAMP defaults to NOW(), so you can simply write UNIX_TIMESTAMP() instead of UNIX_TIMESTAMP(NOW()). I see 2 problems with this approach: 1. This retrieves records starting at the current time on the day which is $days_last days ago. That is, if you run this query at noon, you won't get records from the morning of the first day in the range. That's fine if that's what you want, but I'm not sure that's what Ryan had in mind. 2. By comparing the result of a function of the dat_an_time column, you prevent the use of any index on that column. Depending on the size of the table and the context of the query, that could yield unacceptably slow results. If at all possible, you should compare the value of a column to a constant rather than a function of a column to a function of a constant. With that in mind, if you want time of day to matter, you could rewrite your query as SELECT * FROM tbl_users WHERE dat_an_time FROM_UNIXTIME(UNIX_TIMESTAMP() - $days_last*24*60*60) or, better yet, SELECT * FROM tbl_users WHERE dat_an_time NOW() - INTERVAL $days_last DAY Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp woes
[EMAIL PROTECTED] wrote: Hi, (Please note: NEWBIE WARNING, below questions might sound stupid, but feel free to flame.) ;-) I have a table tbl_users with a field dat_an_time which is a timestamp(14). In that I have values such as: 2004022215 20040227042018 20040223015329 etc I have searched google/the manual for the answers to the below questions but I only found answers if the field is a datetime field and not a timestamp, I cannot change the format as I am the new developer of an old (already live) project and lots of scripts are already accessing this table. Please tell me how to do this with my current setup. Two questions: Question 1. In my php script I am taking the value of days_last which should run a select query to display all the records in the last $days_last days (for those of you who dont program in PHP/Perl $days_last is the variable that is a number which the user selects. eg: 10 or 5 or 35 etc) How do I format the query to get the results from my dat_an_time timestamp(14) field? Somewhat new myself but I believe you can do something like SELECT (whatever you need) FROM tbl_users WHERE (UNIX_TIMESTAMP(now()) - ($days_last*24*60*60)) UNIX_TIMESTAMP(dat_and_tim). I will add the the PHP Cookbook and the MySQL Cookbook are godsends and that's where I got the solution. Grab a copy of these to help out - I've got a project due in a week and I'm using these non-stop. -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp plus 365 days
My first question would be, Why is this column is TIMESTAMP?. It seems to me that if its purpose is to store the subscription start date, it should be of type DATE. Usually, you use a TIMESTAMP column to automatically keep track of the last updated time for a row. Either way (DATE or TIMESTAMP), if you want to add a year to the start_date, you should do just that. Something like UPDATE subscriptions SET start_date = start_date + INTERVAL 1 YEAR WHERE customer_id = ... Don't use +365 days, as some years (leap years) have 366. mysql SELECT CURDATE(), - CURDATE() + INTERVAL 365 DAY `+365 days`, - CURDATE() + INTERVAL 1 YEAR `+1 year`; ++++ | CURDATE() | +365 days | +1 year| ++++ | 2004-02-13 | 2005-02-12 | 2005-02-13 | ++++ 1 row in set (0.01 sec) You also need to decide what to do if a customer renews his or her subscription after it expires. Will you add 1 year to the previous start date, or will you start from the renewal date? Michael Matthew Stuart wrote: Am I able to add 365 days to an already existing TIMESTAMP on a subscription service? If so I was going to use a form on a web page to update it and in the insert statement use Now() + INTERVAL 365 DAY but after some consideration, this would be wrong. This would cause a problem if a current subscriber updated their subscription for another year before their present subscription ran out. In such a case, how do I take a date, eg: 1st March 2004 and add 365 days to it so that no matter when I edit it, it returns the date 1st March 2005? Do I have to change the column type in the database or is it some clever sql - or both? Thanks Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp Problems
Hi Leandro, It is absolutly correct. Please read in doc about timestamp data type http://www.mysql.com/doc/en/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: a.. The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. b.. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) c.. You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW(). 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. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later: a.. Let MySQL set the column when the row is created. This will initialize it to the current date and time. b.. When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value. On the other hand, you may find it just as easy to use a DATETIME column that you initialize to NOW() when the row is created and leave alone for subsequent updates. Best regards, Mikhail. - Original Message - From: Leandro Saggin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:12 PM Subject: Timestamp Problems Hello, I am having problems with columns timestamp, what it happens is the following: I created the following table using timestamp and inserted data in this table: create table con(cod integer not null primary key auto_increment, cod_access integer, hr_con timestamp(14), hr_descon timestamp(14), status char); insert into con values(null,1,'2004010712','20040107120030','D'); when executing select in the table, the data are correct select * from con; ++---+-+ --+--+ | cod | cod_access | hr_con | hr_descon | status | +-+--+-+ --+--+ | 1 | 1 | 2004010712 | 20040107120030 | D | +-+--+-- +--+-+ 1 row in set (0.01 sec) then I make one update in the column hr_descon update con set hr_descon='20040107120100'; there it is the problem, when bringing up to date the column hr_descon for the value informed in update, the column hr_con is brought up to date automatically for current date/time select * from con; select * from con; ++---+-+ --+--+ | cod | cod_access | hr_con | hr_descon | status | +-+--+-+ --+--+ | 1 | 1 | 20040107101056 | 20040107120100 | D| +-+--+-- +--+-+ 1 row in set (0.01 sec) I am using Operational System Solaris 9 on Sparc Platform and I tested in versions 3.23.47, 3.23.58 and 4.0.14 of mysql. If somebody to know what happens please helps I. Thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp Problems
2 (of many) solutions here: Create the column as a datetime instead and set it with NOW() when you first insert the data. In your update query, set the hr_con column to the current value. (If you set the value explicitly it will not be updated with the automatic timestamp value.) read this for a proper description: http://www.mysql.com/doc/en/DATETIME.html Mike On Wednesday 07 January 2004 14.12, Leandro Saggin wrote: Hello, I am having problems with columns timestamp, what it happens is the following: I created the following table using timestamp and inserted data in this table: create table con(cod integer not null primary key auto_increment, cod_access integer, hr_con timestamp(14), hr_descon timestamp(14), status char); insert into con values(null,1,'2004010712','20040107120030','D'); when executing select in the table, the data are correct select * from con; ++---+- +--+--+ | cod | cod_access | hr_con | | hr_descon | status | +-+--+- +--+--+ | 1 | 1 | 2004010712 | | 20040107120030 | D | +-+--+- -+--+-+ 1 row in set (0.01 sec) then I make one update in the column hr_descon update con set hr_descon='20040107120100'; there it is the problem, when bringing up to date the column hr_descon for the value informed in update, the column hr_con is brought up to date automatically for current date/time select * from con; select * from con; ++---+- +--+--+ | cod | cod_access | hr_con | | hr_descon | status | +-+--+- +--+--+ | 1 | 1 | 20040107101056 | | 20040107120100 | D| +-+--+- -+--+-+ 1 row in set (0.01 sec) I am using Operational System Solaris 9 on Sparc Platform and I tested in versions 3.23.47, 3.23.58 and 4.0.14 of mysql. If somebody to know what happens please helps I. Thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp
Mike Blezien [EMAIL PROTECTED] wrote: what is the best way to convert a TIMESTAMP value to a value similar to a DATETIME value ?? If you mean TIMESTAMP column type, you can just change column type with ALTER TABLE statement. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp
i've used FROM_UNIXTIME with success. you can also supply a format. http://www.mysql.com/doc/en/Date_and_time_functions.html hth jeff Mike Blezien [EMAIL PROTECTED]To: MySQL List [EMAIL PROTECTED] net.net cc: Subject: Timestamp 12/07/2003 12:23 PM Please respond to mickalo Hello, what is the best way to convert a TIMESTAMP value to a value similar to a DATETIME value ?? thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- 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 Format in 4.1 alpha
mazur [EMAIL PROTECTED] wrote: Anyone know if there is a way to defeat the new format for the timestamp that appears in 4.1 alpha? I upgraded a MySQL install on a development box that holds a copy of our production data. I quickly saw that the timestamp format was changed from: 20030520124559 , to: 2003-05-20 12:45:59 For better or worse (worse at the moment), I have a large app that is (unfortunately) dependent on the old format...blows up in many places without it actually. Anyone else in the same boat? Any thoughts about this dilemna, other than to say the app should not have been built to be dependent on the old format? :-) Use timestamp_column+0 : http://www.mysql.com/doc/en/Prepare-upgrade-4.0-4.1.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
yes, it automatically inserts the time. From the manual: 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types: ( http://www.mysql.com/doc/en/DATETIME.html ) The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: a.. The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. b.. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) c.. You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW(). - Original Message - From: Tad Ellis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 10:57 PM Subject: timestamp hello and thank you for your time, i have insert into table (column1, column2, create_dt) values (fname, lname, 'what do i put for create_dt?') does timestamp automatically insert the date without me specifying? thank you for your time again, tad -- Addison Ellis small independent publishing co. 114 B 29th Avenue North Nashville, TN 37203 (615) 321-1791 [EMAIL PROTECTED] [EMAIL PROTECTED] subsidiaries of small independent publishing co. [EMAIL PROTECTED] [EMAIL PROTECTED] addisonellis.com THIS E-MAIL AND ANY FILES TRANSMITTED WITH IT ARE CONFIDENTIAL AND ARE INTENDED SOLELY FOR THE INDIVIDUAL OR ENTITY TO WHOM THEY ARE ADDRESSED. IF YOU ARE NOT THE INTENDED RECIPIENT, PLEASE DO NOT READ, COPY OR RE-TRANSMIT THIS COMMUNICATION BUT DESTROY IT IMMEDIATELY. ANY UNAUTHORIZED DISSEMINATION, DISTRIBUTION OR COPYING OF THIS COMMUNICATION IS STRICTLY PROHIBITED. 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: Timestamp and deleteing records.
* [EMAIL PROTECTED] I'm currently building a database and am using a timestamp(14) column for keeping track of when an entry was added to it. Beware that this column also will be updated when you update _any_ column in the row... URL: http://www.mysql.com/doc/en/DATETIME.html Is there an easy way to remove records that are more than 180 days old inside a MySQL query instead of going through and removing tham all by hand, Don't know what you mean by 'removing by hand', I hope you are not editing the .MYD files directly with a hex editor or similar... ;) The SQL statement used to remove rows is called DELETE: URL: http://www.mysql.com/doc/en/DELETE.html To remove rows older than 180 days: DELETE FROM mytable WHERE timestamp_column now() - interval 180 day; HTH, -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Timestamp and deleteing records.
On Thursday 06 March 2003 13:40, wrote: I'm currently building a database and am using a timestamp(14) column for keeping track of when an entry was added to it. Is there an easy way to remove records that are more than 180 days old inside a MySQL query instead of going through and removing tham all by hand, What do you mean removing tham all by hand? Remove records using DELETE statement: http://www.mysql.com/doc/en/DELETE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: TIMESTAMP field is updated unintentionally
Read the section in the manual about timestamps, this is expected behavior, it is how it is supposed to work. http://www.mysql.com/doc/en/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically... -Original Message- From: Marco Deppe [mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 3:18 AM To: [EMAIL PROTECTED] Subject: TIMESTAMP field is updated unintentionally Hi, I was already questioning my sanity, but the problem below is reproduceable: This is how my table looks: mysql describe T_ORDH; --+--+-+++ Field |Type |Null |Key |Default |Extra --+--+-+++ PK_ID |int(10) unsigned | |PRI |NULL|auto_inc ERSTELL_DATUM |timestamp(14) |YES ||NULL| STATUS|smallint(5) unsigned | ||0 | If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP field is updated unintentionally
Marco, mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? The small answer is: It's a feature, and it's documented as well: Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) You explicitly set the TIMESTAMP column to NULL. http://www.mysql.com/doc/en/DATETIME.html BTW: In tables like yours I always have _two_ timestamp columns, like: letzteAenderung TIMESTAMP erstellDatumTIMESTAMP On INSERT, I set erstellDatum to now(). letzteAenderung will automatically be set to the same value. On UPDATE, erstellDatum will be left untouched (because it's not the first TIMESTAMP column), and letzteAenderung will be set to NOW() without me having to think of it. A very convenient feature, indeed :) Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Marco Deppe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 31, 2003 12:17 PM Subject: TIMESTAMP field is updated unintentionally Hi, I was already questioning my sanity, but the problem below is reproduceable: This is how my table looks: mysql describe T_ORDH; --+--+-+++ Field |Type |Null |Key |Default |Extra --+--+-+++ PK_ID |int(10) unsigned | |PRI |NULL|auto_inc ERSTELL_DATUM |timestamp(14) |YES ||NULL| STATUS|smallint(5) unsigned | ||0 | If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) -- Best regards, Marco mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP field is updated unintentionally
At 12:17 +0100 1/31/03, Marco Deppe wrote: Hi, I was already questioning my sanity, Don't. Reading the manual is more helpful. :-) but the problem below is reproduceable: This is how my table looks: mysql describe T_ORDH; --+--+-+++ Field |Type |Null |Key |Default |Extra --+--+-+++ PK_ID |int(10) unsigned | |PRI |NULL|auto_inc ERSTELL_DATUM |timestamp(14) |YES ||NULL| STATUS|smallint(5) unsigned | ||0 | If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? What does the manual say? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? According to the manual, that's how it's supposed to work. Visit the online manual and type TIMESTAMP into the search box. It'll give you the answers you're looking for. (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) -- Best regards, Marco mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP field is updated unintentionally
Since that is exactly how the manual describes it, it must be a feature. If you have more than one timestamp, they will all get set on an insert, but only the first will be changed on an update. Marco Deppe wrote: Hi, I was already questioning my sanity, but the problem below is reproduceable: This is how my table looks: mysql describe T_ORDH; --+--+-+++ Field |Type |Null |Key |Default |Extra --+--+-+++ PK_ID |int(10) unsigned | |PRI |NULL|auto_inc ERSTELL_DATUM |timestamp(14) |YES ||NULL| STATUS|smallint(5) unsigned | ||0 | If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP field is updated unintentionally
Hi. On Fri 2003-01-31 at 12:17:42 +0100, [EMAIL PROTECTED] wrote: I was already questioning my sanity, but the problem below is reproduceable: [...] If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? [...] The big question: Is it a bug or a feature? A feature, it is described in detail in the section that explains the TIMESTAMP column type: http://www.mysql.com/doc/en/DATETIME.html If you don't want that behaviour, use DATETIME with NOW() instead. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP field is updated unintentionally
At 12:17 PM +0100 1/31/03, Marco Deppe wrote: Hi, I was already questioning my sanity, but the problem below is reproduceable: This is how my table looks: mysql describe T_ORDH; --+--+-+++ Field |Type |Null |Key |Default |Extra --+--+-+++ PK_ID |int(10) unsigned | |PRI |NULL|auto_inc ERSTELL_DATUM |timestamp(14) |YES ||NULL| STATUS|smallint(5) unsigned | ||0 | If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) From: http://www.mysql.com/doc/en/DATETIME.html Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: snip # You explicitly set the TIMESTAMP column to NULL ...so that means it's a feature. -steve -- ++ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | ++ | SETI@Home: 1001 Work units on 23 oct 2002 | | 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens... | ++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: TIMESTAMP field is updated unintentionally
Timestamp columns update automatically. http://www.mysql.com/doc/en/DATETIME.html Peter ^_^ -Original Message- From: Marco Deppe [mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 6:18 AM To: [EMAIL PROTECTED] Subject: TIMESTAMP field is updated unintentionally Hi, I was already questioning my sanity, but the problem below is reproduceable: This is how my table looks: mysql describe T_ORDH; --+--+-+++ Field |Type |Null |Key |Default |Extra --+--+-+++ PK_ID |int(10) unsigned | |PRI |NULL|auto_inc ERSTELL_DATUM |timestamp(14) |YES ||NULL| STATUS|smallint(5) unsigned | ||0 | If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) -- Best regards, Marco mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP field is updated unintentionally
Hi, It is a feature, the first TIMESTAMP field is automatically updated each time you update the record. Check the manual for details: http://www.mysql.com/doc/en/DATETIME.html If you want mysql to automatically set it at creation time only, your workaround is OK. You can also convert ERSTELL_DATUM to DATETIME type and set is explicitely to NOW() when you insert a new record. Hope this helps -- Joseph Bueno Marco Deppe wrote: Hi, I was already questioning my sanity, but the problem below is reproduceable: This is how my table looks: mysql describe T_ORDH; --+--+-+++ Field |Type |Null |Key |Default |Extra --+--+-+++ PK_ID |int(10) unsigned | |PRI |NULL|auto_inc ERSTELL_DATUM |timestamp(14) |YES ||NULL| STATUS|smallint(5) unsigned | ||0 | If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp records
* [EMAIL PROTECTED] I have a table of records using timestamp(14) as a field and need to remove any records that are 60 days past the timestamp. I've looked at the manual and can't find anything relating on doing this. What query would I need to run on the database? To remove records, use the DELETE command: URL: http://www.mysql.com/doc/en/DELETE.html To remove records that are more than 60 days old: DELETE FROM table WHERE timestamp_field NOW() - INTERVAL 60 DAY; This syntax is valid for version 3.23 and later, for version 3.22 you need to use something like this: DELETE FROM table WHERE timestamp_field DATE_SUB(NOW(),INTERVAL 60 DAY); The NOW() function returns the current timestamp. There are many other usefull date and time functions available, see the manual: URL: http://www.mysql.com/doc/en/Date_and_time_functions.html -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp records
Thanks, Ed On Mon, 6 Jan 2003, Roger Baklund wrote: * [EMAIL PROTECTED] I have a table of records using timestamp(14) as a field and need to remove any records that are 60 days past the timestamp. I've looked at the manual and can't find anything relating on doing this. What query would I need to run on the database? To remove records, use the DELETE command: URL: http://www.mysql.com/doc/en/DELETE.html To remove records that are more than 60 days old: DELETE FROM table WHERE timestamp_field NOW() - INTERVAL 60 DAY; This syntax is valid for version 3.23 and later, for version 3.22 you need to use something like this: DELETE FROM table WHERE timestamp_field DATE_SUB(NOW(),INTERVAL 60 DAY); The NOW() function returns the current timestamp. There are many other usefull date and time functions available, see the manual: URL: http://www.mysql.com/doc/en/Date_and_time_functions.html -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TimeStamp in MySQL reqd NULL
On 23 Dec 2002, at 16:11, Akash wrote: According to MySQL implementation, if I give the default value of the column during table creation as NULL, it will store the current time in the timestamp column. I do not want this current time to be stored in the timestamp column. I want it to be NULL. How to do this ? If you don't like the properties of TIMESTAMP columns, don't use them. Use DATETIME instead, or use an INT and store Unix time in it (thus avoiding some complications of time zones and daylight saving time). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What, if anything, is wrong with UNIX Epoch time stamps? [Was: RE: TimeStamp in MySQL reqd NULL]
This is a related but different question/comment. In the six or so years of using MySQL, my various teams have never used anything except an unsigned INT and UNIX Epoch time to record timestamps. The given is, of course, we have always been a Perl/C/C++/UNIX shop, and we will be for a long time, at least. :-) I have looked at the growing list of MySQL date/time functions from time to time, and have always come to the conclusion that Epoch time is the best way for us to store date/time. Another given is that we don't need to save sub-second time stamps. I won't go into the pros of Epoch time. I'll just say that we've never found it wanting. I guess the cost is having queries like where ts (other_ts - (3600 * 24 * 7)), etc. Perl, our primary language, makes at an absolute breeze to convert to/from Epoch time. Can anyone share and/or comment about the use of Epoch time, especially the hazards? I'm asking because I want to make sure that I haven't built a comfortable little box that un-necessarily excludes some useful functions. :-) Cheers. -Dana -Original Message- From: Keith C. Ivey [SMTP:[EMAIL PROTECTED]] Sent: Monday, December 23, 2002 9:16 AM To: [EMAIL PROTECTED] Cc: Akash Subject: Re: TimeStamp in MySQL reqd NULL On 23 Dec 2002, at 16:11, Akash wrote: According to MySQL implementation, if I give the default value of the column during table creation as NULL, it will store the current time in the timestamp column. I do not want this current time to be stored in the timestamp column. I want it to be NULL. How to do this ? If you don't like the properties of TIMESTAMP columns, don't use them. Use DATETIME instead, or use an INT and store Unix time in it (thus avoiding some complications of time zones and daylight saving time). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php ** This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. ** Wal-Mart Stores, Inc. Confidential ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: TimeStamp in MySQL reqd NULL
On Monday 23 December 2002 12:41, Akash wrote: I want to store NULLS or '0' in a column which is of type TimeStamp. According to MySQL implementation, if I give the default value of the column during table creation as NULL, it will store the current time in the timestamp column. I do not want this current time to be stored in the timestamp column. I want it to be NULL. How to do this ? Use DATETIME column type instead: http://www.mysql.com/doc/en/DATETIME.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What, if anything, is wrong with UNIX Epoch time stamps? [Was: RE: TimeStamp in MySQL reqd NULL]
Date/Time is such a tricky thing. I think that's we migrated toward the simplest solution in the first place. For days/weeks/months, I think the math cited below works pretty well. That is, if we're not talking about calendar months. As soon as we need to query based on calendar things, we tend to get the start and end epoch times from something like Perl's Time::ParseDate, which is fluent in the madness that is the Gregorian calendar, and query against such limits. Cheers. -Dana -Original Message- From: Michael T. Babcock [SMTP:[EMAIL PROTECTED]] Sent: Monday, December 23, 2002 10:57 AM To: [EMAIL PROTECTED] Cc: Dana Diederich; 'Keith C. Ivey'; [EMAIL PROTECTED]; Akash Subject: Re: What, if anything, is wrong with UNIX Epoch time stamps? [Was: R E: TimeStamp in MySQL reqd NULL] Csongor Fagyal wrote: Oh and one more thing I am not really sure of: sometimes defining the day as 3600*24 or the year as 3600*24*365 is not the best idea... just think about leap years. How do you handle that? And there are some more artifacts in the Gregorian calendar, too... But MySQL doesn't guarantee correctness in time values in the first place. You can still insert 2002-02-31 as a date if you like: mysql create table temp (date datetime); Query OK, 0 rows affected mysql insert into temp(date) values (2002-02-31); Query OK, 1 row affected mysql select * from temp; +-+ | date| +-+ | 2002-02-31 00:00:00 | +-+ 1 row in set -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock ** This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. ** Wal-Mart Stores, Inc. Confidential ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP null value help
At 19:32 -0600 11/25/02, Ronald Petty wrote: I am trying to get my TIMESTAMPE field to auto update (use the current time for inserts). However it keeps going to all 00, I read the documentation and it says that is because it is getting an invalid input. However it also states if you put null in it will work, it does on the server, but when I use perl/dbi it goes to 00 That's because to specify NULL in DBI, you should use undef. Here is the code my $sth1 = $dbh-prepare(insert into CONTACT values (?,?,?,?,?,?,?,?)); $sth1-execute($query-param(NAME), $query-param(COMPANY), $query-param(EMAIL), $query-param(PHONE), $quer y-param(ADDRESS), $query-param(SUBJECT), $query-param(MESSAGE), null) or die Can't execute SQL statement: $ DBI::errstr\n; and the results for the ones using the form about have all zeros, if I do insert into CONTACT (NAME,COMPANY,EMAIL,PHONE,ADDRESS,SUBJECT,MESSAGE,DATE) values (Ron Petty, asdfasdf, [EMAIL PROTECTED], asdfddd, sdfsadfasdfsad, Testing contact, Hey did this thing work?, null); It works, any ideas? Thanks Ron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp updated on select
Ok, here we are, in much detail. There is a table called translations and another one called poems. Their structure is given in the end. The query (given by a php script by the way) is: select poems.poem_id, language, translation_title,made_by_id from poems,translations where poems.poem_id=translations.poem_id order by date_added desc limit 0,10 and with this I intend to retrieve the 10 latest additions of translations. However, when running the script, I noticed that always, on the top of the results was the translation link I had clicked last. And this happens all the time. Now that I think of it again, the clicked link contains a mysql query which increments the translation views by one. H.maybe this does the unwanted update CREATE TABLE `poems` ( `poem_id` smallint(4) unsigned NOT NULL auto_increment, `poem_title` varchar(38) default NULL, `poet_id` smallint(4) unsigned default NULL, `comments` varchar(255) default NULL, `poem_views` smallint(5) unsigned NOT NULL default '0', `poem` text, `poem_comment` text NOT NULL, PRIMARY KEY (`poem_id`) ) TYPE=MyISAM and CREATE TABLE `translations` ( `poem_id` smallint(4) unsigned NOT NULL default '0', `language` varchar(14) NOT NULL default 'english', `translation_title` varchar(54) default NULL, `translation_text` text, `footnotes` text, `made_by_id` smallint(4) unsigned NOT NULL default '0', `contributed` varchar(70) default NULL, `translation_views` smallint(5) unsigned NOT NULL default '0', `date_added` timestamp(14) NOT NULL, PRIMARY KEY (`poem_id`,`language`,`made_by_id`), KEY `language` (`language`) ) TYPE=MyISAM Paul DuBois wrote: At 2:09 +0200 11/7/02, Galanis Nikolas wrote: Yes, that is exactly what happens. It is updated with a simple select statement. It is a select from a table which contains only one timestamp column and the order by is made by this timstamp column. And every time the value if the timestamp is updated. That seems fairly strange. Let's see an example. On Wed, 6 Nov 2002, Paul DuBois wrote: At 23:47 + 11/6/02, Nikolas Galanis wrote: Hello I have a column of type timestamp(14) and I thought it would not be updated on a query with simple select statements, though it does! I read in the manual that it shouldn't, what could be wrong? Thanks. You're saying that performing a SELECT on the table causes the TIMESTAMP column to *change*? That shouldn't happen. -- - - Nikolas Galanis - - Computer Engineering Informatics Department - - University of Patras - - http://www.ceid.upatras.gr- - 061 997707- - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp updated on select
Nikolas Galanis wrote: and with this I intend to retrieve the 10 latest additions of translations. However, when running the script, I noticed that always, on the top of the results was the translation link I had clicked last. And this happens all the time. Now that I think of it again, the clicked link contains a mysql query which increments the translation views by one. H.maybe this does the unwanted update Any change to the record will update the timestamp; what you probably want is a seperate counting table for views instead; PoemViews ( poemid smallint unsigned not null primary key, count smallint unsigned not null default 0 ); ... Then do a REPLACE INTO PoemViews (poemid, count) SELECT 14, count FROM PoemViews WHERE poemid = 14 ... or whatever (where 14 is an arbitrary number in this case; I'd use a server-side variable, personally). You could also make a views table thats more universal to the site (which is what I do) and then point to it from your poems table (ViewsID type thing) ... your choice. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp updated on select
At 11:41 + 11/7/02, Nikolas Galanis wrote: Ok, here we are, in much detail. There is a table called translations and another one called poems. Their structure is given in the end. The query (given by a php script by the way) is: select poems.poem_id, language, translation_title,made_by_id from poems,translations where poems.poem_id=translations.poem_id order by date_added desc limit 0,10 and with this I intend to retrieve the 10 latest additions of translations. However, when running the script, I noticed that always, on the top of the results was the translation link I had clicked last. And this happens all the time. Now that I think of it again, the clicked link contains a mysql query which increments the translation views by one. H.maybe this does the unwanted update Exactly. To prevent this, you can modify your UPDATE query to set the TIMESTAMP column to its current value. That will prevent it from being updated to the current date and time automatically. I don't know what your query looks like exactly, but you can write it something like this: UPDATE translations SET date_added = date_added, translation_views = translation_views + 1 ... CREATE TABLE `poems` ( `poem_id` smallint(4) unsigned NOT NULL auto_increment, `poem_title` varchar(38) default NULL, `poet_id` smallint(4) unsigned default NULL, `comments` varchar(255) default NULL, `poem_views` smallint(5) unsigned NOT NULL default '0', `poem` text, `poem_comment` text NOT NULL, PRIMARY KEY (`poem_id`) ) TYPE=MyISAM and CREATE TABLE `translations` ( `poem_id` smallint(4) unsigned NOT NULL default '0', `language` varchar(14) NOT NULL default 'english', `translation_title` varchar(54) default NULL, `translation_text` text, `footnotes` text, `made_by_id` smallint(4) unsigned NOT NULL default '0', `contributed` varchar(70) default NULL, `translation_views` smallint(5) unsigned NOT NULL default '0', `date_added` timestamp(14) NOT NULL, PRIMARY KEY (`poem_id`,`language`,`made_by_id`), KEY `language` (`language`) ) TYPE=MyISAM Paul DuBois wrote: At 2:09 +0200 11/7/02, Galanis Nikolas wrote: Yes, that is exactly what happens. It is updated with a simple select statement. It is a select from a table which contains only one timestamp column and the order by is made by this timstamp column. And every time the value if the timestamp is updated. That seems fairly strange. Let's see an example. On Wed, 6 Nov 2002, Paul DuBois wrote: At 23:47 + 11/6/02, Nikolas Galanis wrote: Hello I have a column of type timestamp(14) and I thought it would not be updated on a query with simple select statements, though it does! I read in the manual that it shouldn't, what could be wrong? Thanks. You're saying that performing a SELECT on the table causes the TIMESTAMP column to *change*? That shouldn't happen. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp updated on select
Hello I have a column of type timestamp(14) and I thought it would not be updated on a query with simple select statements, though it does! I read in the manual that it shouldn't, what could be wrong? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp updated on select
At 23:47 + 11/6/02, Nikolas Galanis wrote: Hello I have a column of type timestamp(14) and I thought it would not be updated on a query with simple select statements, though it does! I read in the manual that it shouldn't, what could be wrong? Thanks. You're saying that performing a SELECT on the table causes the TIMESTAMP column to *change*? That shouldn't happen. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp updated on select
Yes, that is exactly what happens. It is updated with a simple select statement. It is a select from a table which contains only one timestamp column and the order by is made by this timstamp column. And every time the value if the timestamp is updated. On Wed, 6 Nov 2002, Paul DuBois wrote: At 23:47 + 11/6/02, Nikolas Galanis wrote: Hello I have a column of type timestamp(14) and I thought it would not be updated on a query with simple select statements, though it does! I read in the manual that it shouldn't, what could be wrong? Thanks. You're saying that performing a SELECT on the table causes the TIMESTAMP column to *change*? That shouldn't happen. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp updated on select
At 2:09 +0200 11/7/02, Galanis Nikolas wrote: Yes, that is exactly what happens. It is updated with a simple select statement. It is a select from a table which contains only one timestamp column and the order by is made by this timstamp column. And every time the value if the timestamp is updated. That seems fairly strange. Let's see an example. On Wed, 6 Nov 2002, Paul DuBois wrote: At 23:47 + 11/6/02, Nikolas Galanis wrote: Hello I have a column of type timestamp(14) and I thought it would not be updated on a query with simple select statements, though it does! I read in the manual that it shouldn't, what could be wrong? Thanks. You're saying that performing a SELECT on the table causes the TIMESTAMP column to *change*? That shouldn't happen. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp field in the InnoDB table
Devi, As I understand you need to update timestamp field in some tables. And you have only 4-5 milliseconds for it? Is it correct? What is the problem? Best regards, Mikhail. - Original Message - From: Devi Annisetty [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 3:08 PM Subject: RE: Timestamp field in the InnoDB table Hi, This is almost my requirement.I have to update the exact timestamps(most critical for me) and I will have only 4- 5 mts available to update different tables. So how can I do that with out much time difference. I am new to MYSQL. Any help would be greatly appreciated. Thanks Devi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp field in the InnoDB table
Heikki, thank you for your response. Is this a big problem? Well, actually it is not very big problem for me now, because I already know about that feature. ;) But I would prefer to have timestamp that means the end of the transaction. Anyway it would be very useful to have some notices in the documentation. Thank you. Best regards, Mikhail. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:20 PM Subject: Re: Timestamp field in the InnoDB table Mikhail, - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:11 PM Subject: Timestamp field in the InnoDB table Hi, I have found one unclear place for me regarding to the timestamp field in the InnoDB table. Please, explain me am I correct or not. Let's say we have a table Test with 'timestamp' field: CREATE TABLE `Test` ( `id` int(3) NOT NULL auto_increment, `UpdateDate` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; and I try to update one row in this table: update Test set UpdateDate = NULL where id = 1; Let's say I started 'update' statement at 15:00:00. But the row with id = 1 is blocked by another transaction, so 'update' statement needs to wait till the end of the transaction. After 10 sec the block on the record with id = 1 is released. So my 'update' finished. select UpdateDate from Test where id = 1 gives me 2002102115, but I would expect 20021021150010. So what do you think about it? looks like the MySQL interpreter assigns the clock time value to the timestamp field before calling the InnoDB backend. Is this a big problem? Thanks in advance. Best regards, Mikhail. Regards, Heikki sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp field in the InnoDB table
Mikhail, - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 2:34 PM Subject: Re: Timestamp field in the InnoDB table Heikki, thank you for your response. Is this a big problem? Well, actually it is not very big problem for me now, because I already know about that feature. ;) But I would prefer to have timestamp that means the end of the transaction. that is difficult, because then we would need a completion procedure at the end of a transaction which would go to set the timestamps to the transaction commit timepoint. Anyway it would be very useful to have some notices in the documentation. Thank you. Best regards, Mikhail. Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:20 PM Subject: Re: Timestamp field in the InnoDB table Mikhail, - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:11 PM Subject: Timestamp field in the InnoDB table Hi, I have found one unclear place for me regarding to the timestamp field in the InnoDB table. Please, explain me am I correct or not. Let's say we have a table Test with 'timestamp' field: CREATE TABLE `Test` ( `id` int(3) NOT NULL auto_increment, `UpdateDate` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; and I try to update one row in this table: update Test set UpdateDate = NULL where id = 1; Let's say I started 'update' statement at 15:00:00. But the row with id = 1 is blocked by another transaction, so 'update' statement needs to wait till the end of the transaction. After 10 sec the block on the record with id = 1 is released. So my 'update' finished. select UpdateDate from Test where id = 1 gives me 2002102115, but I would expect 20021021150010. So what do you think about it? looks like the MySQL interpreter assigns the clock time value to the timestamp field before calling the InnoDB backend. Is this a big problem? Thanks in advance. Best regards, Mikhail. Regards, Heikki sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp field in the InnoDB table
Mikhail, - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:11 PM Subject: Timestamp field in the InnoDB table Hi, I have found one unclear place for me regarding to the timestamp field in the InnoDB table. Please, explain me am I correct or not. Let's say we have a table Test with 'timestamp' field: CREATE TABLE `Test` ( `id` int(3) NOT NULL auto_increment, `UpdateDate` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; and I try to update one row in this table: update Test set UpdateDate = NULL where id = 1; Let's say I started 'update' statement at 15:00:00. But the row with id = 1 is blocked by another transaction, so 'update' statement needs to wait till the end of the transaction. After 10 sec the block on the record with id = 1 is released. So my 'update' finished. select UpdateDate from Test where id = 1 gives me 2002102115, but I would expect 20021021150010. So what do you think about it? looks like the MySQL interpreter assigns the clock time value to the timestamp field before calling the InnoDB backend. Is this a big problem? Thanks in advance. Best regards, Mikhail. Regards, Heikki sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: timestamp bug increments by one day
isn't that the month changing? it's changing from october to january...?? Alan -Original Message- From: Jay X [mailto:sparqz50;hotmail.com] Sent: Monday, 21 October 2002 12:20 To: [EMAIL PROTECTED] Subject: timestamp bug increments by one day Hi There, Just reciently (after no changes) my MySQL database is automatically incrementing timestamps by one day. Timestamp format is timestamp(14). An example date would be 20021021143513 when I use command such as: INSERT INTO MyTable SET timestamp = 200210211143513; and then: SELECT * FROM MyTable; I get : timestamp 200201221143513 This is happening on Redhat 7.1, MySQL Ver 11.15 Distrib 3.23.38, for pc-linux-gnu (i686) when I type: SELECT NOW(); I get the correct date, and when I type: INSERT INTO MyTable SET timestamp = NOW(); that also increments the timestamp by one day just like it does when I set the timestamp manually. Thanks, Stuart _ Surf the Web without missing calls! Get MSN Broadband. http://resourcecenter.msn.com/access/plans/freeactivation.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp issue
Prafulla, please check documentation http://www.mysql.com/doc/en/DATETIME.html ... The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. ... You need to change type of fields from timestamp to datetime. Best regards, Mikhail. - Original Message - From: Prafulla Girgaonkar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 17, 2002 1:19 PM Subject: Timestamp issue Mysql Version:: Server version 3.23.47-nt Protocol version10 Connection . via named pipe UNIX socket MySQL OS:: Windows NT 4.0 Workstation Hello Following is the description of the problem being faced. Problem: The first timestamp column in a table is set to current date-time value as soon as we update one or more columns in the table. Example: The guest information is stored in a table named GUEST. The schema for it is as below. create table guest (guestID int, arrivalDate timestamp, departureDate timestamp, status char(1)); Above table is populated using following queries. insert into guest values (1, '2002-09-30', '2002-10-14', 'Y'); insert into guest values (2, '2002-09-20', '2002-10-01', 'Y'); insert into guest values (3, '2002-09-15', '2002-09-25', 'Y'); insert into guest values (4, '2002-09-12', '2002-09-20', 'Y'); If we use following query to update the status from Y to N, then arrivalDate column is set to CURRENT(system) date-time value. update guest set status = 'N'; Does anybody have any information on this issue? Thanx in advance. Prafulla - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Timestamp issue in mysql
Prafulla, Tuesday, September 17, 2002, 2:12:58 PM, you wrote: PG Following is the description of the problem being faced. PG Problem: PG The first timestamp column in a table is set to current date-time value as soon as we update one or more columns in the table. It's a normal behaviour of TIMESTAMP column: http://www.mysql.com/doc/en/DATETIME.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: timestamp problem ..
The first timestamp in any table is automatically updated by mysql every time you modify that record. It is the modify timestamp If you wish to use the timestamp in your table, you should create two timestamps at least and use the second one Modstamp timestamp Usable timestamp See here http://www.mysql.com/doc/D/A/DATETIME.html starting on the fourth paragraph I'll take my beer via air mail :) -Original Message- From: Wouter van Vliet [mailto:[EMAIL PROTECTED]] Sent: Friday, June 14, 2002 9:53 AM To: [EMAIL PROTECTED] Cc: Wouter @ Witbier Subject: timestamp problem .. Heey Folks, I'm having a slight problem with the timestamp column format. When I alter a table and, add a column of type timestamp all records get the current timestamp, that's ok. When i insert a new row, all records get the current timestamp. That too is ok. But now, when I update one row of the table, that row gets a new timestamp. And that's not what i'd like it to do. Does somebody have any idea on how this can be prevented? I've tried to make the column of type int(14) and then set now() or UNIX_TIMESTAMP as default value, but that just results in a very well known error 1064 (You have an error in your SQL syntax near 'NOW()' at line 1). Thanks ! Wouter (ps. beer for the helper .. if you'd come up with some idea to give it to you) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp problem ..
Wouter, Friday, June 14, 2002, 4:53:20 PM, you wrote: WvV I'm having a slight problem with the timestamp column format. When I alter a WvV table and, add a column of type timestamp all records get the current WvV timestamp, that's ok. When i insert a new row, all records get the current WvV timestamp. That too is ok. But now, when I update one row of the table, that WvV row gets a new timestamp. And that's not what i'd like it to do. Does WvV somebody have any idea on how this can be prevented? WvV I've tried to make the column of type int(14) and then set now() or WvV UNIX_TIMESTAMP as default value, but that just results in a very well known WvV error 1064 (You have an error in your SQL syntax near 'NOW()' at line 1). You can add one more TIMESTAMP column or take a look at DATETIME column and use functon NOW(): http://www.mysql.com/doc/D/A/DATETIME.html WvV Thanks ! WvV Wouter -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp problem ..
This is exactly what timestamp columns are supposed to do - see http://www.mysql.com/doc/D/A/DATETIME.html The first timestamp column in the table will be automatically updated upon insert/update. Your choices are: (1) Change to datetime type. Then, on insert, insert the current date via now(). It will not be updated unless you specifically change it. (2) Add a second timestamp column; only the FIRST timestamp column is updated as in (1) above. See the docs for mor info. (3) Keep the column type as timestamp, but always explicitly insert the desired date/time - that way, the value won't be automatically set. This is probably the least desireable option, though. If I were me, I'd go with (1). Or, depending on your needs, a combination of timestamp datetime columns. Now about that beer... ;) -steve At 3:53 PM +0200 6/14/02, Wouter van Vliet wrote: Heey Folks, I'm having a slight problem with the timestamp column format. When I alter a table and, add a column of type timestamp all records get the current timestamp, that's ok. When i insert a new row, all records get the current timestamp. That too is ok. But now, when I update one row of the table, that row gets a new timestamp. And that's not what i'd like it to do. Does somebody have any idea on how this can be prevented? I've tried to make the column of type int(14) and then set now() or UNIX_TIMESTAMP as default value, but that just results in a very well known error 1064 (You have an error in your SQL syntax near 'NOW()' at line 1). Thanks ! Wouter (ps. beer for the helper .. if you'd come up with some idea to give it to you) -- ++ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | ++ | If only life would imitate toys. | | - Ted Raimi, March 2002 | | - http://www.whoosh.org/issue67/friends67a.html#raimi | ++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: timestamp problem ..
whow, this list is so very powerfull .. just give me your address and I'll send you the beer .. what kind of it would you like? -Oorspronkelijk bericht- Van: Luc Foisy [mailto:[EMAIL PROTECTED]] Verzonden: June 14, 2002 16:04 Aan: Wouter van Vliet; MYSQL-List (E-mail) Onderwerp: RE: timestamp problem .. The first timestamp in any table is automatically updated by mysql every time you modify that record. It is the modify timestamp If you wish to use the timestamp in your table, you should create two timestamps at least and use the second one Modstamp timestamp Usable timestamp See here http://www.mysql.com/doc/D/A/DATETIME.html starting on the fourth paragraph I'll take my beer via air mail :) -Original Message- From: Wouter van Vliet [mailto:[EMAIL PROTECTED]] Sent: Friday, June 14, 2002 9:53 AM To: [EMAIL PROTECTED] Cc: Wouter @ Witbier Subject: timestamp problem .. Heey Folks, I'm having a slight problem with the timestamp column format. When I alter a table and, add a column of type timestamp all records get the current timestamp, that's ok. When i insert a new row, all records get the current timestamp. That too is ok. But now, when I update one row of the table, that row gets a new timestamp. And that's not what i'd like it to do. Does somebody have any idea on how this can be prevented? I've tried to make the column of type int(14) and then set now() or UNIX_TIMESTAMP as default value, but that just results in a very well known error 1064 (You have an error in your SQL syntax near 'NOW()' at line 1). Thanks ! Wouter (ps. beer for the helper .. if you'd come up with some idea to give it to you) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp
r,
Thursday, May 30, 2002, 12:35:58 PM, you wrote:
r in a timestamp field, do I have to provide the values?
r eg
r create table ryan(t_imestamp timestamp(8), name varchar(30));
r how do I insert? do I have to specify the value? if so how do I get the
r value to specify it?
r The manual has just confused me more.
You can insert into the timestamp field current time or specific
value. If you want to insert current date and time you set the column
to NULL or to NOW(), f.e.:
INSERT INTO ryan VALUES (NULL,'some string');
or
INSERT INTO ryan VALUES (NOW(),'some string');
You can also insert into column certain value:
INSERT INTO ryan VALUES ('20020417','some string');
Note: Timestamp(8) means that only 8 digits will be displayed, not
stored! So, if you have 2 values f.e. '2002041700' and
'2002041701' they will be both shows as '20020417' but they are
not the same.
r Cheers,
r -Ryan
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp(8) GROUP BY problem
try timestamp(14) instead. [EMAIL PROTECTED] wrote: Description: Problem with timestamp(8) and GROUP BY How-To-Repeat: mysql CREATE TABLE test ( - id int auto_increment, - dd timestamp(8), - data int, - PRIMARY KEY (id) - ); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO test (data) VALUES(1),(2),(3),(4); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql SELECT * FROM test; ++--+--+ | id | dd | data | ++--+--+ | 1 | 20020527 |1 | | 2 | 20020527 |2 | | 3 | 20020527 |3 | | 4 | 20020527 |4 | ++--+--+ 4 rows in set (0.00 sec) mysql SELECT dd,count(*) FROM test GROUP BY dd; +--+--+ | dd | count(*) | +--+--+ | 20020527 |4 | +--+--+ 1 row in set (0.00 sec) mysql UPDATE test SET dd='2002052701' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql UPDATE test SET dd='2002052702' WHERE id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql UPDATE test SET dd='2002052703' WHERE id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql UPDATE test SET dd='2002052704' WHERE id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql SELECT dd,count(*) FROM test GROUP BY dd; +--+--+ | dd | count(*) | +--+--+ | 20020527 |1 | | 20020527 |1 | | 20020527 |1 | | 20020527 |1 | +--+--+ 4 rows in set (0.00 sec) Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49a-max-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket/tmp/mysql.sock Uptime:65 days 4 hours 9 min 10 sec Threads: 6 Questions: 69973912 Slow queries: 688 Opens: 129933 Flush tables: 3 Open tables: 506 Queries per second avg: 12.427 Environment: System: Linux gap 2.4.18 #3 SMP Fri Mar 15 14:40:03 EET 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1384040 Dec 18 18:24 /lib/libc.so.6 -rw-r--r--1 root root 25214756 Dec 18 18:07 /usr/lib/libc.a -rw-r--r--1 root root 178 Dec 18 18:07 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-innodb --with-berkeley-db --enable-thread-safe-client --with-other-libc=/usr/local/mysql-glibc '--with-comment=Official MySQL Binary' --prefix=/usr/local/mysql --with-extra-charset=complex --enable-thread-safe-client --enable-local-infile --with-server-suffix=-max 'CFLAGS=-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-r! tti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX=gcc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check:
Re: timestamp(8) GROUP BY problem
sitnikov, Monday, May 27, 2002, 10:32:07 PM, you wrote: s Description: s Problem with timestamp(8) and GROUP BY s How-To-Repeat: mysql CREATE TABLE test ( s - id int auto_increment, s - dd timestamp(8), s - data int, s - PRIMARY KEY (id) s - ); s Query OK, 0 rows affected (0.01 sec) [skip] TIMESTAMP(8) is a display size, not the size of field. If you change column to TIMESTAMP(14) you can see that you have different values for dd. mysql alter table test change dd dd timestamp(14); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql SELECT dd,count(*) FROM test GROUP BY dd; ++--+ | dd | count(*) | ++--+ | 2002052701 |1 | | 2002052702 |1 | | 2002052703 |1 | | 2002052704 |1 | ++--+ 4 rows in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timestamp(8) GROUP BY problem
On 27 May 2002, at 22:32, [EMAIL PROTECTED] wrote: mysql SELECT dd,count(*) FROM test GROUP BY dd; +--+--+ | dd | count(*) | +--+--+ | 20020527 |1 | | 20020527 |1 | | 20020527 |1 | | 20020527 |1 | +--+--+ 4 rows in set (0.00 sec) The 8 in timestamp(8) affects only the display. The other digits are still there internally, so the values are still distinct when you attempt to group them. Perhaps what you want is something like SELECT LEFT(dd, 8), COUNT(*) GROUP BY LEFT(dd, 8); Also, if you're not using the automatic-update property of TIMESTAMP, you might want to use a DATE or DATETIME column instead. [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: timestamp primary key's value improperly changing on sql update
The is the proper, documented functionality of the timestamp field. An exerpt from http://www.mysql.com/doc/D/A/DATETIME.html is below: The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) You explicitly set the TIMESTAMP column to NULL. Duncan -- Duncan Salada | Titan | www.titan.com/testeval Email: [EMAIL PROTECTED] | Voice: 301-925-3222x375 | Fax: 301-925-3216 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 11:05 AM To: [EMAIL PROTECTED] Subject: timestamp primary key's value improperly changing on sql update Description: Given a table that has a timestamp field which is either the primary key or one field in a multi-column primary key, the value in this field is updated to the current time any time an SQL UPDATE is done, even when the timestamp field is not intended to be updated. How-To-Repeat: mysql CREATE TABLE foo(field1 TIMESTAMP PRIMARY KEY, field2 INT); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO foo VALUES(now(), 10); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM foo; +++ | field1 | field2 | +++ | 20020522105353 | 10 | +++ 1 row in set (0.00 sec) mysql UPDATE foo SET field2=11; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql SELECT * FROM foo; +++ | field1 | field2 | +++ | 20020522105405 | 11 | +++ 1 row in set (0.00 sec) -- NOTICE that the time in field1 changed after the update. This is wrong. Fix: A workaround is to set the timestamp field to itself on an update. ex: UPDATE foo SET field1=field1, field2=11 Submitter-Id: Originator: Jeff Messner Organization: MySQL support: none Synopsis:timestamp primary key's value improperly changing on sql update Severity:serious Priority:medium Category:mysql Class: sw-bug Release: mysql-3.23.49a (Official MySQL Binary) Server: /usr/local/mysql/bin/mysqladmin Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version3.23.49a Protocol version 10 ConnectionLocalhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 29 days 45 min 32 sec Threads: 1 Questions: 2658 Slow queries: 0 Opens: 28 Flush tables: 1 Open tables: 18 Queries per second avg: 0.001 Environment: System: Linux pc-00183 2.4.7-10 #1 Thu Sep 6 17:21:28 EDT 2001 i586 unknown Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 11 12:11 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 5716491 Sep 4 2001 /lib/libc-2.2.4.so -rw-r--r--1 root root 27304836 Sep 4 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 4 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex
Re: TIMESTAMP field
Hello. On Mon, May 20, 2002 at 03:22:12PM -0400, [EMAIL PROTECTED] wrote: Is it possible to alter or modify a timestamp field to a different date? Yes. Bye, Benjamin. PS: If you expected a different answer, you may want to consider to elaborate a bit. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP field : nevermind
At 3:22 PM -0400 5/20/02, Alex Pilson wrote: Is it possible to alter or modify a timestamp field to a different date? Doh. I found the answer... The answer is yes. -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso 5 Web Development/Hosting // MySQL Development // Apple DVD Studio Pro Authoring/Training // Macromedia Director/Flash Authoring --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
