| Bug #8212 | UNIX_TIMESTAMP returns 0 instead of negative ints for dates between 1901-1970 | ||
|---|---|---|---|
| Submitted: | 31 Jan 2005 14:15 | Modified: | 4 Feb 2005 11:45 |
| Reporter: | Peter van Dijk | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0.18 | OS: | FreeBSD (FreeBSD 4.9-RELEASE-p4) |
| Assigned to: | CPU Architecture: | Any | |
[4 Feb 2005 11:40]
Dmitry Lenev
Hi, Peter! Actually POSIX does not say that time_t type which is type of return value of mktime()/time() C-functions after which MySQL's UNIX_TIMESTAMP() function was designed should be signed (actually it does not even require it to be integer but that is different story). So it does not require from implementations to correctly operate with datetime values which correspond to negative time_t values. Moreover it uses (time_t)-1 as error-indicating return value from these functions. (Example of platform where time_t is unsigned and where "negative" time_t (i.e. greater than 2^31-1) values correspond to dates in 21st century is QNX 6.) This means we simply can't implement UNIX_TIMESTAMP() which would accept datetime values prior to 1970-01-01 UTC in portable way and that MySQL matches POSIX in this sense. Probably we can implement simple wrapper over system's mktime() call which will meet your expectations on some systems... But this is definitely feature request and not a bug...
[4 Feb 2005 11:45]
Dmitry Lenev
Thus changing status of this bug report to "Not a Bug". Feel free to change its severity to "Feature request". Thank you for you interest in MySQL, Peter!
[4 Feb 2005 13:17]
Peter van Dijk
I can't seem to change the severity; could you change it to Feature request for me? Thank you for your time!
Description: -bash-2.05b$ gdate -d '1970-01-01 12:00:00' +%s 39600 -bash-2.05b$ mysql -e 'select unix_timestamp("1970-01-01 12:00:00")' +---------------------------------------+ | unix_timestamp("1970-01-01 12:00:00") | +---------------------------------------+ | 39600 | +---------------------------------------+ -bash-2.05b$ gdate -d '1960-01-01 12:00:00' +%s -315579600 -bash-2.05b$ mysql -e 'select unix_timestamp("1960-01-01 12:00:00")' +---------------------------------------+ | unix_timestamp("1960-01-01 12:00:00") | +---------------------------------------+ | 0 | +---------------------------------------+ MySQL's UNIX_TIMESTAMP function does not handle dates before 1970, returning '0'. This matches the documentation, but does not match my expectation and, I suspect, POSIX. How to repeat: See 'Description' Suggested fix: My suggestion is to add another function, p.e. UNIX_REAL_TIMESTAMP, that does have the expected behaviour, and then add some warnings to the UNIX_TIMESTAMP documentation. Changing UNIX_TIMESTAMP itself might break too many current applications.