List:Commits« Previous MessageNext Message »
From:paul Date:January 20 2006 7:35pm
Subject:svn commit - mysqldoc@docsrva: r953 - in trunk: . refman-4.1 refman-5.0 refman-5.1
View as plain text  
Author: paul
Date: 2006-01-20 20:35:49 +0100 (Fri, 20 Jan 2006)
New Revision: 953

Log:
 r6487@frost:  paul | 2006-01-20 13:05:34 -0600
 UNIX_TIMESTAMP() <-> FROM_UNIXTIME() round-tripping is lossy. (Bug#15654)


Modified:
   trunk/
   trunk/refman-4.1/functions.xml
   trunk/refman-5.0/functions.xml
   trunk/refman-5.1/functions.xml


Property changes on: trunk
___________________________________________________________________
Name: svk:merge
   - b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6486
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2387
   + b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6487
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2387

Modified: trunk/refman-4.1/functions.xml
===================================================================
--- trunk/refman-4.1/functions.xml	2006-01-20 19:35:33 UTC (rev 952)
+++ trunk/refman-4.1/functions.xml	2006-01-20 19:35:49 UTC (rev 953)
@@ -7174,14 +7174,16 @@
           in <literal>'YYYY-MM-DD HH:MM:SS'</literal> or
           <literal>YYYYMMDDHHMMSS</literal> format, depending on whether
           the function is used in a string or numeric context.
+          <replaceable>unix_timestamp</replaceable> is an internal
+          timestamp value such as is produced by the
+          <literal>UNIX_TIMESTAMP()</literal> function.
         </para>
 
         <para>
           If <replaceable>format</replaceable> is given, the result is
           formatted according to the <replaceable>format</replaceable>
-          string. <replaceable>format</replaceable> may contain the same
-          specifiers as those listed in the entry for the
-          <literal>DATE_FORMAT()</literal> function.
+          string, which is used the same way as listed in the entry for
+          the <literal>DATE_FORMAT()</literal> function.
         </para>
 
         <remark role="help-description-end"/>
@@ -7193,15 +7195,19 @@
         -&gt; '1997-10-04 22:23:00'
 mysql&gt; <userinput>SELECT FROM_UNIXTIME(875996580) + 0;</userinput>
         -&gt; 19971004222300
-</programlisting>
-
-        <remark role="help-example"/>
-
-<programlisting>
 mysql&gt; <userinput>SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),</userinput>
     -&gt;                      <userinput>'%Y %D %M %h:%i:%s %x');</userinput>
         -&gt; '2003 6th August 06:22:58 2003'
 </programlisting>
+
+        <para>
+          Note: If you use <literal>UNIX_TIMESTAMP()</literal> and
+          <literal>FROM_UNIXTIME()</literal> to convert between
+          <literal>TIMESTAMP</literal> values and Unix timestamp values,
+          the conversion is lossy because the mapping is not one-to-one
+          in both directions. For details, see the description of the
+          <literal>UNIX_TIMESTAMP()</literal> function.
+        </para>
       </listitem>
 
       <listitem>
@@ -8468,16 +8474,20 @@
 
         <para>
           If called with no argument, returns a Unix timestamp (seconds
-          since <literal>'1970-01-01 00:00:00'</literal> GMT) as an
+          since <literal>'1970-01-01 00:00:00'</literal> UTC) as an
           unsigned integer. If <literal>UNIX_TIMESTAMP()</literal> is
           called with a <replaceable>date</replaceable> argument, it
           returns the value of the argument as seconds since
-          <literal>'1970-01-01 00:00:00'</literal> GMT.
+          <literal>'1970-01-01 00:00:00'</literal> UTC.
           <replaceable>date</replaceable> may be a
           <literal>DATE</literal> string, a <literal>DATETIME</literal>
           string, a <literal>TIMESTAMP</literal>, or a number in the
           format <literal>YYMMDD</literal> or
-          <literal>YYYYMMDD</literal> in local time.
+          <literal>YYYYMMDD</literal>. The server interprets
+          <replaceable>date</replaceable> as a value in the current time
+          zone and converts it to an internal value in UTC. Clients can
+          set their time zone as described in
+          <xref linkend="time-zone-support"/>.
         </para>
 
         <remark role="help-description-end"/>
@@ -8506,6 +8516,45 @@
         </para>
 
         <para>
+          Note: If you use <literal>UNIX_TIMESTAMP()</literal> and
+          <literal>FROM_UNIXTIME()</literal> to convert between
+          <literal>TIMESTAMP</literal> values and Unix timestamp values,
+          the conversion is lossy because the mapping is not one-to-one
+          in both directions. For example, due to conventions for local
+          time zone changes, it is possible for two
+          <literal>UNIX_TIMESTAMP()</literal> to map two
+          <literal>TIMESTAMP</literal> values to the same Unix timestamp
+          value. <literal>FROM_UNIXTIME()</literal> will map that value
+          back to only one of the original <literal>TIMESTAMP</literal>
+          values. Here is an example, using <literal>TIMESTAMP</literal>
+          values in the <literal>CET</literal> time zone:
+        </para>
+
+<programlisting>
+<!--
+mysql> SET time_zone = 'CET';
+-->
+mysql&gt; <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
++---------------------------------------+
+|                            1111885200 |
++---------------------------------------+
+mysql&gt; <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
++---------------------------------------+
+|                            1111885200 |
++---------------------------------------+
+mysql&gt; <userinput>SELECT FROM_UNIXTIME(1111885200);</userinput>
++---------------------------+
+| FROM_UNIXTIME(1111885200) |
++---------------------------+
+| 2005-03-27 03:00:00       |
++---------------------------+
+</programlisting>
+
+        <para>
           If you want to subtract <literal>UNIX_TIMESTAMP()</literal>
           columns, you might want to cast the result to signed integers.
           See <xref linkend="cast-functions"/>.

Modified: trunk/refman-5.0/functions.xml
===================================================================
--- trunk/refman-5.0/functions.xml	2006-01-20 19:35:33 UTC (rev 952)
+++ trunk/refman-5.0/functions.xml	2006-01-20 19:35:49 UTC (rev 953)
@@ -7234,14 +7234,16 @@
           in <literal>'YYYY-MM-DD HH:MM:SS'</literal> or
           <literal>YYYYMMDDHHMMSS</literal> format, depending on whether
           the function is used in a string or numeric context.
+          <replaceable>unix_timestamp</replaceable> is an internal
+          timestamp value such as is produced by the
+          <literal>UNIX_TIMESTAMP()</literal> function.
         </para>
 
         <para>
           If <replaceable>format</replaceable> is given, the result is
           formatted according to the <replaceable>format</replaceable>
-          string. <replaceable>format</replaceable> may contain the same
-          specifiers as those listed in the entry for the
-          <literal>DATE_FORMAT()</literal> function.
+          string, which is used the same way as listed in the entry for
+          the <literal>DATE_FORMAT()</literal> function.
         </para>
 
         <remark role="help-description-end"/>
@@ -7253,15 +7255,19 @@
         -&gt; '1997-10-04 22:23:00'
 mysql&gt; <userinput>SELECT FROM_UNIXTIME(875996580) + 0;</userinput>
         -&gt; 19971004222300
-</programlisting>
-
-        <remark role="help-example"/>
-
-<programlisting>
 mysql&gt; <userinput>SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),</userinput>
     -&gt;                      <userinput>'%Y %D %M %h:%i:%s %x');</userinput>
         -&gt; '2003 6th August 06:22:58 2003'
 </programlisting>
+
+        <para>
+          Note: If you use <literal>UNIX_TIMESTAMP()</literal> and
+          <literal>FROM_UNIXTIME()</literal> to convert between
+          <literal>TIMESTAMP</literal> values and Unix timestamp values,
+          the conversion is lossy because the mapping is not one-to-one
+          in both directions. For details, see the description of the
+          <literal>UNIX_TIMESTAMP()</literal> function.
+        </para>
       </listitem>
 
       <listitem>
@@ -8602,16 +8608,20 @@
 
         <para>
           If called with no argument, returns a Unix timestamp (seconds
-          since <literal>'1970-01-01 00:00:00'</literal> GMT) as an
+          since <literal>'1970-01-01 00:00:00'</literal> UTC) as an
           unsigned integer. If <literal>UNIX_TIMESTAMP()</literal> is
           called with a <replaceable>date</replaceable> argument, it
           returns the value of the argument as seconds since
-          <literal>'1970-01-01 00:00:00'</literal> GMT.
+          <literal>'1970-01-01 00:00:00'</literal> UTC.
           <replaceable>date</replaceable> may be a
           <literal>DATE</literal> string, a <literal>DATETIME</literal>
           string, a <literal>TIMESTAMP</literal>, or a number in the
           format <literal>YYMMDD</literal> or
-          <literal>YYYYMMDD</literal> in local time.
+          <literal>YYYYMMDD</literal>. The server interprets
+          <replaceable>date</replaceable> as a value in the current time
+          zone and converts it to an internal value in UTC. Clients can
+          set their time zone as described in
+          <xref linkend="time-zone-support"/>.
         </para>
 
         <remark role="help-description-end"/>
@@ -8632,14 +8642,49 @@
           <quote>string-to-Unix-timestamp</quote> conversion. If you
           pass an out-of-range date to
           <literal>UNIX_TIMESTAMP()</literal>, it returns
-          <literal>0</literal>, but please note that only basic range
-          checking is performed (year from <literal>1970</literal> to
-          <literal>2037</literal>, month from <literal>01</literal> to
-          <literal>12</literal>, day from <literal>01</literal> from
-          <literal>31</literal>).
+          <literal>0</literal>.
         </para>
 
         <para>
+          Note: If you use <literal>UNIX_TIMESTAMP()</literal> and
+          <literal>FROM_UNIXTIME()</literal> to convert between
+          <literal>TIMESTAMP</literal> values and Unix timestamp values,
+          the conversion is lossy because the mapping is not one-to-one
+          in both directions. For example, due to conventions for local
+          time zone changes, it is possible for two
+          <literal>UNIX_TIMESTAMP()</literal> to map two
+          <literal>TIMESTAMP</literal> values to the same Unix timestamp
+          value. <literal>FROM_UNIXTIME()</literal> will map that value
+          back to only one of the original <literal>TIMESTAMP</literal>
+          values. Here is an example, using <literal>TIMESTAMP</literal>
+          values in the <literal>CET</literal> time zone:
+        </para>
+
+<programlisting>
+<!--
+mysql> SET time_zone = 'CET';
+-->
+mysql&gt; <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
++---------------------------------------+
+|                            1111885200 |
++---------------------------------------+
+mysql&gt; <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
++---------------------------------------+
+|                            1111885200 |
++---------------------------------------+
+mysql&gt; <userinput>SELECT FROM_UNIXTIME(1111885200);</userinput>
++---------------------------+
+| FROM_UNIXTIME(1111885200) |
++---------------------------+
+| 2005-03-27 03:00:00       |
++---------------------------+
+</programlisting>
+
+        <para>
           If you want to subtract <literal>UNIX_TIMESTAMP()</literal>
           columns, you might want to cast the result to signed integers.
           See <xref linkend="cast-functions"/>.

Modified: trunk/refman-5.1/functions.xml
===================================================================
--- trunk/refman-5.1/functions.xml	2006-01-20 19:35:33 UTC (rev 952)
+++ trunk/refman-5.1/functions.xml	2006-01-20 19:35:49 UTC (rev 953)
@@ -7188,14 +7188,16 @@
           in <literal>'YYYY-MM-DD HH:MM:SS'</literal> or
           <literal>YYYYMMDDHHMMSS</literal> format, depending on whether
           the function is used in a string or numeric context.
+          <replaceable>unix_timestamp</replaceable> is an internal
+          timestamp value such as is produced by the
+          <literal>UNIX_TIMESTAMP()</literal> function.
         </para>
 
         <para>
           If <replaceable>format</replaceable> is given, the result is
           formatted according to the <replaceable>format</replaceable>
-          string. <replaceable>format</replaceable> may contain the same
-          specifiers as those listed in the entry for the
-          <literal>DATE_FORMAT()</literal> function.
+          string, which is used the same way as listed in the entry for
+          the <literal>DATE_FORMAT()</literal> function.
         </para>
 
         <remark role="help-description-end"/>
@@ -7207,15 +7209,19 @@
         -&gt; '1997-10-04 22:23:00'
 mysql&gt; <userinput>SELECT FROM_UNIXTIME(875996580) + 0;</userinput>
         -&gt; 19971004222300
-</programlisting>
-
-        <remark role="help-example"/>
-
-<programlisting>
 mysql&gt; <userinput>SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),</userinput>
     -&gt;                      <userinput>'%Y %D %M %h:%i:%s %x');</userinput>
         -&gt; '2003 6th August 06:22:58 2003'
 </programlisting>
+
+        <para>
+          Note: If you use <literal>UNIX_TIMESTAMP()</literal> and
+          <literal>FROM_UNIXTIME()</literal> to convert between
+          <literal>TIMESTAMP</literal> values and Unix timestamp values,
+          the conversion is lossy because the mapping is not one-to-one
+          in both directions. For details, see the description of the
+          <literal>UNIX_TIMESTAMP()</literal> function.
+        </para>
       </listitem>
 
       <listitem>
@@ -8546,16 +8552,20 @@
 
         <para>
           If called with no argument, returns a Unix timestamp (seconds
-          since <literal>'1970-01-01 00:00:00'</literal> GMT) as an
+          since <literal>'1970-01-01 00:00:00'</literal> UTC) as an
           unsigned integer. If <literal>UNIX_TIMESTAMP()</literal> is
           called with a <replaceable>date</replaceable> argument, it
           returns the value of the argument as seconds since
-          <literal>'1970-01-01 00:00:00'</literal> GMT.
+          <literal>'1970-01-01 00:00:00'</literal> UTC.
           <replaceable>date</replaceable> may be a
           <literal>DATE</literal> string, a <literal>DATETIME</literal>
           string, a <literal>TIMESTAMP</literal>, or a number in the
           format <literal>YYMMDD</literal> or
-          <literal>YYYYMMDD</literal> in local time.
+          <literal>YYYYMMDD</literal>. The server interprets
+          <replaceable>date</replaceable> as a value in the current time
+          zone and converts it to an internal value in UTC. Clients can
+          set their time zone as described in
+          <xref linkend="time-zone-support"/>.
         </para>
 
         <remark role="help-description-end"/>
@@ -8576,14 +8586,49 @@
           <quote>string-to-Unix-timestamp</quote> conversion. If you
           pass an out-of-range date to
           <literal>UNIX_TIMESTAMP()</literal>, it returns
-          <literal>0</literal>, but please note that only basic range
-          checking is performed (year from <literal>1970</literal> to
-          <literal>2037</literal>, month from <literal>01</literal> to
-          <literal>12</literal>, day from <literal>01</literal> from
-          <literal>31</literal>).
+          <literal>0</literal>.
         </para>
 
         <para>
+          Note: If you use <literal>UNIX_TIMESTAMP()</literal> and
+          <literal>FROM_UNIXTIME()</literal> to convert between
+          <literal>TIMESTAMP</literal> values and Unix timestamp values,
+          the conversion is lossy because the mapping is not one-to-one
+          in both directions. For example, due to conventions for local
+          time zone changes, it is possible for two
+          <literal>UNIX_TIMESTAMP()</literal> to map two
+          <literal>TIMESTAMP</literal> values to the same Unix timestamp
+          value. <literal>FROM_UNIXTIME()</literal> will map that value
+          back to only one of the original <literal>TIMESTAMP</literal>
+          values. Here is an example, using <literal>TIMESTAMP</literal>
+          values in the <literal>CET</literal> time zone:
+        </para>
+
+<programlisting>
+<!--
+mysql> SET time_zone = 'CET';
+-->
+mysql&gt; <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
++---------------------------------------+
+|                            1111885200 |
++---------------------------------------+
+mysql&gt; <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
++---------------------------------------+
+|                            1111885200 |
++---------------------------------------+
+mysql&gt; <userinput>SELECT FROM_UNIXTIME(1111885200);</userinput>
++---------------------------+
+| FROM_UNIXTIME(1111885200) |
++---------------------------+
+| 2005-03-27 03:00:00       |
++---------------------------+
+</programlisting>
+
+        <para>
           If you want to subtract <literal>UNIX_TIMESTAMP()</literal>
           columns, you might want to cast the result to signed integers.
           See <xref linkend="cast-functions"/>.

Thread
svn commit - mysqldoc@docsrva: r953 - in trunk: . refman-4.1 refman-5.0 refman-5.1paul20 Jan