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 @@
-> '1997-10-04 22:23:00'
mysql> <userinput>SELECT FROM_UNIXTIME(875996580) + 0;</userinput>
-> 19971004222300
-</programlisting>
-
- <remark role="help-example"/>
-
-<programlisting>
mysql> <userinput>SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),</userinput>
-> <userinput>'%Y %D %M %h:%i:%s %x');</userinput>
-> '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> <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
++---------------------------------------+
+| 1111885200 |
++---------------------------------------+
+mysql> <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
++---------------------------------------+
+| 1111885200 |
++---------------------------------------+
+mysql> <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 @@
-> '1997-10-04 22:23:00'
mysql> <userinput>SELECT FROM_UNIXTIME(875996580) + 0;</userinput>
-> 19971004222300
-</programlisting>
-
- <remark role="help-example"/>
-
-<programlisting>
mysql> <userinput>SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),</userinput>
-> <userinput>'%Y %D %M %h:%i:%s %x');</userinput>
-> '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> <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
++---------------------------------------+
+| 1111885200 |
++---------------------------------------+
+mysql> <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
++---------------------------------------+
+| 1111885200 |
++---------------------------------------+
+mysql> <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 @@
-> '1997-10-04 22:23:00'
mysql> <userinput>SELECT FROM_UNIXTIME(875996580) + 0;</userinput>
-> 19971004222300
-</programlisting>
-
- <remark role="help-example"/>
-
-<programlisting>
mysql> <userinput>SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),</userinput>
-> <userinput>'%Y %D %M %h:%i:%s %x');</userinput>
-> '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> <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
++---------------------------------------+
+| 1111885200 |
++---------------------------------------+
+mysql> <userinput>SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');</userinput>
++---------------------------------------+
+| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
++---------------------------------------+
+| 1111885200 |
++---------------------------------------+
+mysql> <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.1 | paul | 20 Jan |
