[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: timestamp and DST: impossible to backup database? *AND* bugs
From:       Peter_Valdemar_Mørch <swp5jhu02 () sneakemail ! com>
Date:       2004-11-29 23:12:30
Message-ID: 41ABACDE.3000003 () sneakemail ! com
[Download RAW message or body]

Thank you Michael for your very thoughtful reply. I know that it takes 
time and effort to answer at the level you did.

Michael Stassen Michael.Stassen-at-verizon.net |Lists| wrote:
 > You seem to have a fundamental misunderstanding of the TIMESTAMP type.
 > No timezone or DST information is stored in a TIMESTAMP column.

Yup. I thought it could be used to unambiguously represent any and all 
points in time. It can't. Thats it in a nutshell.

I need to be able to sort, get and set the time unambiguously, also 
during the one "problem hour" in october. I need to know that if I put 
in a field with a "time value" I can reliably retrieve it again. And 
that if a record went in at time X and another in at time Y, Y-X is 
accurate for all values of Y and X, regardless of how we humans have 
decided to present X and Y to each other. (Standard computer stuff, no?)

DATETIME is ambiguous, seconds since epoch UTC is not.

Maybe my surprise is more: Hey, depending on now(), a 
UNIX_TIMESTAMP("2004-10-31 02:15:00") has two different "interal" 
values!!! (Why now() should have any effect on that is still weird to 
me... I realize *how* it ends up having an effect implementationally, 
but it *shouldn't*.) The "other" value is not representable at all by 
any DATETIME value. And sorting on a DATETIME gives one result now and 
another after a dump/restore cycle. And there is no way around that.

We'll change our application to int(32) unsigned and handle presentation 
client-side. I don't think I'll ever use a DATETIME again... Maybe thats 
just me.

Thanks again, Michael.

Peter

-- 
Peter Valdemar Mørch
http://www.morch.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]