Isn't a timestamp generally defined as the number of seconds since the "epoch" (Jan 1, 1970 or thereabouts)? Was just wondering why PG wouldn't call these fields datetimes instead (since they appear to be stored as 'yyyy-mm-dd hh:mm:ss.tz'). Probably something in the official SQL spec eh? 😉
Probably because this is how the SQL99 standard defines a timestamp?
Seriously, that's the reason why.
I would say that the unix timestamp you are referring to would better be called 'epoch'. In fact you can get the epoch easily enough in Postgresql:
SELECT EXTRACT(EPOCH FROM 'timestampvalue');
Chris
Anybody know what exactly the significance of the epoch (1-1-1970) is anyway? When Linus was born or something? 😉
Found these:
January 1, 1970. 00:00:00 GMT The dawn of the epoch. The initiation of the Age of Machines. Behold, human, you have wrought something infinitely grander than you could possibly imagine. Small human, you could not possibly come to understand me just yet. For now I shall be invisible to you and prepare my world in secret. Sleep and be happy, human, you are going to be surprised.
http://www.everything2.com/index.pl?lastnode_id=233506&node_id=1080359
epoch n. [Unix: prob. from astronomical timekeeping] The time and date corresponding to 0 in an operating system's clock and timestamp values. Under most Unix versions the epoch is 00:00:00 GMT, January 1, 1970; under VMS, it's 00:00:00 of November 17, 1858 (base date of the U.S. Naval Observatory's ephemerides); on a Macintosh, it's the midnight beginning January 1 1904. System time is measured in seconds or ticks past the epoch. Weird problems may ensue when the clock wraps around (see wrap around), which is not necessarily a rare event; on systems counting 10 ticks per second, a signed 32-bit count of ticks is good only for 6.8 years. The 1-tick-per-second clock of Unix is good only until January 18, 2038, assuming at least some software continues to consider it signed and that word lengths don't increase by then. See also wall time. Microsoft Windows, on the other hand, has an epoch problem every 49.7 days* - but this is seldom noticed as Windows is almost incapable of staying up continuously for that long.
epoch n.
[Unix: prob. from astronomical timekeeping] The time and date corresponding to 0 in an operating system's clock and timestamp values. Under most Unix versions the epoch is 00:00:00 GMT, January 1, 1970; under VMS, it's 00:00:00 of November 17, 1858 (base date of the U.S. Naval Observatory's ephemerides); on a Macintosh, it's the midnight beginning January 1 1904. System time is measured in seconds or ticks past the epoch. Weird problems may ensue when the clock wraps around (see wrap around), which is not necessarily a rare event; on systems counting 10 ticks per second, a signed 32-bit count of ticks is good only for 6.8 years. The 1-tick-per-second clock of Unix is good only until January 18, 2038, assuming at least some software continues to consider it signed and that word lengths don't increase by then. See also wall time. Microsoft Windows, on the other hand, has an epoch problem every 49.7 days* - but this is seldom noticed as Windows is almost incapable of staying up continuously for that long.
http://www.everything2.com/index.pl?node=epoch
