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
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]
Fw: TimeStamp issue
I'm putting this back on the list where it belongs; that enables everyone to benefit from the discussion, both now and in the future via the archives. -- Sorry, you're right, I didn't read your entire question thoroughly. You set the default value for a DATETIME column (or any other type for that matter) in the CREATE TABLE statement. For example: create table if not exists dates03 (id smallint not null default 99, my_datetime datetime not null default '2006-03-02 12:34:56', primary key(id)); Please note that a datetime value can be expressed in several different formats. The different formats are explained here: http://dev.mysql.com/doc/refman/5.0/en/datetime.html. -- Rhino - Original Message - From: rtroiana [EMAIL PROTECTED] To: 'Rhino' [EMAIL PROTECTED] Sent: Thursday, March 02, 2006 1:29 PM Subject: RE: TimeStamp issue Thanks for the reply. I'm using DATETIME instead of TIMESTAMP now. Although all I wanted to know was if there's any way I can set default values to DATETIME column. ++Reema -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 1:26 PM To: rtroiana; mysql@lists.mysql.com Subject: 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 -- 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]
Timestamp issue in mysql
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
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
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
Critical TIMESTAMP issue?
Hi everyone, TIMESTAMP types are supposed to contain millis and even nonos. How can I ensure these are set, retrieved and used for ORDER BY and comparisons in MySQL? I cannot find a way to get even millis back. Also MySQL does not seem to order by this information. See below example. Is this a bug? Is there a work around? My application requires to catch inter-second events and to maintain their order. Also what is the SQL standard way to manipulate below sec columns. Your help is very appreciated. Please reply to me directly (as well). Gunter Leeb Output: mysql insert MMEReason(ID, Modified, Event_ref) values(-1, '2001-09-26 16:08:09.01299', -1); Query OK, 1 row affected (0.00 sec) mysql insert MMEReason(ID, Modified, Event_ref) values(-2, '2001-09-26 16:08:09.01399', -1); Query OK, 1 row affected (0.00 sec) mysql insert MMEReason(ID, Modified, Event_ref) values(-3, '2001-09-26 16:08:09.01199', -1); Query OK, 1 row affected (0.00 sec) mysql select ID, Modified from MMEReason order by modified; +++ | ID | Modified | +++ | -3 | 20010926160809 | | -2 | 20010926160809 | | -1 | 20010926160809 | +++ 3 rows in set (0.00 sec) Environment: Mysql: V. 3.23.41-max-nt JDBC MM-mysql. V.2.0.6 recent Microsoft JVM Win2K Gunter Leeb Director of Development MachineGeneration Inc. [EMAIL PROTECTED] Direct: (512) 266 2139 Main: (512) 266 9580 - 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: Critical TIMESTAMP issue?
On Wed, Sep 26, 2001 at 04:53:18PM -0500, Gunter Leeb wrote: Hi everyone, TIMESTAMP types are supposed to contain millis and even nonos. Not according to the documentation. How can I ensure these are set, retrieved and used for ORDER BY and comparisons in MySQL? There is no built-in type that will automatically handle the time values you're looking for. You'll have to generate them in your application. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 20 days, processed 394,201,857 queries (217/sec. avg) - 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
