List:General Discussion« Previous MessageNext Message »
From:gordon Date:September 9 1999 1:40am
Subject:date_format of a timestamp column loses century
View as plain text  
>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
Thread
date_format of a timestamp column loses centurygordon9 Sep
  • Re: date_format of a timestamp column loses centuryJoshua Chamas9 Sep
    • Re: date_format of a timestamp column loses centuryMichael Widenius9 Sep