>Description:
I want to print a timestamp(14) value in the same format
that a datetime value is formatted. It seems that
date_format(stamp, '%Y-%m-%d %H:%i:%S') should do this.
However, I get the year 0099 rather than 1999, even though
the full year is clearly present in the timestamp field.
>How-To-Repeat:
Create a table with a single timestamp(14) column. Insert a
few null values, which become the current time. Display the
column in native and formatted versions. Note incorrect year.
mysql> describe stamplog;
+-------+---------------+------+-----+---------+-------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges
|
+-------+---------------+------+-----+---------+-------+---------------------------------+
| stamp | timestamp(14) | YES | | NULL | | select,insert,update,references
|
+-------+---------------+------+-----+---------+-------+---------------------------------+
mysql> insert into stamplog values (null);
mysql> insert into stamplog values (null);
mysql> insert into stamplog values (null);
mysql> select stamp, date_format(stamp, '%Y-%m-%d %H:%i:%S') from stamplog;
+----------------+-----------------------------------------+
| stamp | date_format(stamp, '%Y-%m-%d %H:%i:%S') |
+----------------+-----------------------------------------+
| 19990908202048 | 0099-09-08 20:20:48 |
| 19990908202055 | 0099-09-08 20:20:55 |
| 19990908202101 | 0099-09-08 20:21:01 |
+----------------+-----------------------------------------+
Note display of year 0099, rather than 1999.
>Fix:
None known, other than using %y rather than %Y and getting icky
non-Y2K-compliant 2-digit years in the output.
>Submitter-Id: <submitter ID>
>Originator: Gordon Burditt
>Organization:
>MySQL support: none
>Synopsis: date_format of a timestamp column loses century
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.2-alpha (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 7.11 Distrib 3.23.2-alpha, for -freebsd3.2 on
> i386
TCX Datakonsult AB, by Monty
Server version 3.23.2-alpha-log
Protocol version 10
Connection hammy.lonestar.org via TCP/IP
TCP port 3306
Uptime: 28 days 8 hours 39 min 37 sec
Threads: 1 Questions: 184481 Slow queries: 6 Opens: 68 Flush tables: 1 Open tables:
26
>Environment:
System: FreeBSD gerbil.hammy.lonestar.org 3.2-RELEASE FreeBSD 3.2-RELEASE #0: Sun Jun 27
12:30:12 CDT 1999 gordon@stripped:/usr/src/sys/compile/GERBIL i386
Some paths: /usr/local/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: gcc version 2.7.2.1
Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS=''
Configure command: ./configure --with-unix-socket-path=/var/tmp/mysql.sock
--with-low-memory --with-mit-threads=yes
Perl: This is perl, version 5.005_02 built for i386-freebsd
