[SQL] Datetime
Hello, I'm using a TIMESTAMP column with a now() default which (correctly i assume) uses a '-mm-dd hh:mm:ss' format. Is it possible to make it something like '-mm-dd hh:mm:ss:cc' ? (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and not .c as the TIME format. Thanks, \\pb ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Datetime
On Aug 3, 2004, at 4:22 PM, Pedro B. wrote: Is it possible to make it something like '-mm-dd hh:mm:ss:cc' ? (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and not .c as the TIME format. timestamp and timestamptz both take an optional precision parameter. What you want is timestamp(2) or timestamptz(2), I believe. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Datetime
Hi, On Tue, 3 Aug 2004, Pedro B. wrote: > I'm using a TIMESTAMP column with a now() default which (correctly i > assume) uses a '-mm-dd hh:mm:ss' format. > > Is it possible to make it something like '-mm-dd hh:mm:ss:cc' ? > (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and > not .c as the TIME format. Is this what you are asking? test=> SELECT now()::timestamp(1); now 2004-08-03 13:58:48.60 (1 row) Regards, -- Devrim GUNDUZ devrim~gunduz.org devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Datetime
O kyrios Pedro B. egrapse stis Aug 3, 2004 : > Hello, > > I'm using a TIMESTAMP column with a now() default which (correctly i > assume) uses a '-mm-dd hh:mm:ss' format. Wrong!, timestamp does not use any human readable format to be stored. Its not like MS* tools where dates/times are actually text. now() returns the current UNIX (your running UNIX right?) timestamp which in turn is measured in seconds,miliseconds since the epoch. i.e. 1970-01-01 00:00:00 > > Is it possible to make it something like '-mm-dd hh:mm:ss:cc' ? > (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and > not .c as the TIME format. > > Thanks, > \\pb > > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Datetime
On Aug 3, 2004, at 7:23 PM, Achilleus Mantzios wrote: now() returns the current UNIX (your running UNIX right?) timestamp which in turn is measured in seconds,miliseconds since the epoch. i.e. 1970-01-01 00:00:00 I believe this is incorrect. I believe PostgreSQL uses its own timestamp datatype internally (which is, indeed, not as text in an easy-to-read form). On my machine (running cvs-head), test=# select now(); now --- 2004-08-03 20:27:18.822646+09 (1 row) which is definitely not seconds.milliseconds since epoch. You can use extract to get seconds.milliseconds from epoch, but I don't think this is how it's stored internally. test=# select extract(epoch from now()); date_part - 1091532506.3222 (1 row) Just some additional trivia, current_timestamp is an SQL-spec compliant alias for now(), and might be a better choice if one is concerned with compatibility. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Datetime
O kyrios Michael Glaesemann egrapse stis Aug 3, 2004 : > > On Aug 3, 2004, at 7:23 PM, Achilleus Mantzios wrote: > > > now() returns the current UNIX (your running UNIX right?) timestamp > > which > > in turn is > > measured in seconds,miliseconds since the epoch. > > i.e. 1970-01-01 00:00:00 > > I believe this is incorrect. I believe PostgreSQL uses its own Do you suggest postgresql has any other means of getting time except the time(2) syscall?? > timestamp datatype internally (which is, indeed, not as text in an > easy-to-read form). On my machine (running cvs-head), > > test=# select now(); >now > --- > 2004-08-03 20:27:18.822646+09 > (1 row) > > which is definitely not seconds.milliseconds since epoch. You can use > extract to get seconds.milliseconds from epoch, but I don't think this > is how it's stored internally. > > test=# select extract(epoch from now()); > date_part > - > 1091532506.3222 > (1 row) > > Just some additional trivia, current_timestamp is an SQL-spec compliant > alias for now(), and might be a better choice if one is concerned with > compatibility. > > Michael Glaesemann > grzm myrealbox com > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- -Achilleus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Datetime
On Aug 3, 2004, at 8:50 PM, Achilleus Mantzios wrote: O kyrios Michael Glaesemann egrapse stis Aug 3, 2004 : I believe this is incorrect. I believe PostgreSQL uses its own Do you suggest postgresql has any other means of getting time except the time(2) syscall?? timestamp datatype internally (which is, indeed, not as text in an easy-to-read form). On my machine (running cvs-head), No. I'm just saying that PostgreSQL does not represent or store timestamps as epoch timestamps internally. I don't know for sure how PostgreSQL gets the current timestamp, and I wouldn't be surprised if it was via the time(2) syscall. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Datetime
Michael Glaesemann <[EMAIL PROTECTED]> writes: > No. I'm just saying that PostgreSQL does not represent or store > timestamps as epoch timestamps internally. You're wrong. It's not exactly Unix-like because we use a different epoch date (2000-1-1 not 1970-1-1) but the concept is just the same: what's stored is the number of seconds before or after the epoch. The default is to store this as a double precision number (hence supporting fractional seconds, with a machine-dependent amount of precision) but you can compile the server to use 64-bit integers instead. In that case the integer value actually represents microseconds before or after the epoch, and so the precision is fixed at microseconds. What you see when you display the value is an external textual representation, not the internal form. This is generally true for all Postgres datatypes except text/varchar/char ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Datetime
On Aug 4, 2004, at 12:13 AM, Tom Lane wrote: Michael Glaesemann <[EMAIL PROTECTED]> writes: No. I'm just saying that PostgreSQL does not represent or store timestamps as epoch timestamps internally. You're wrong. It's not exactly Unix-like because we use a different epoch date (2000-1-1 not 1970-1-1) but the concept is just the same: what's stored is the number of seconds before or after the epoch. The default is to store this as a double precision number (hence supporting fractional seconds, with a machine-dependent amount of precision) but you can compile the server to use 64-bit integers instead. In that case the integer value actually represents microseconds before or after the epoch, and so the precision is fixed at microseconds. As I understood Achilleus, he said that PostgreSQL used UNIX epoch timestamp internally, which is defined as seconds from 1970-01-01. What I said is that PostgreSQL does not use UNIX epoch internally, which is exactly what you've verified. PostgreSQL uses seconds and microseconds from 2000-01-01, and PostgreSQL can be compiled to use 64-bit integers (rather than double precision floats) to represent integer microseconds from 2000-01-01. Thank you for explaining these things. However, I don't quite understand how I am wrong in saying that PostgreSQL does not use UNIX epoch timestamps internally, as you've clearly explained it doesn't. What you see when you display the value is an external textual representation, not the internal form. Which I don't think was ever at issue. Thanks again for explaining the internals. I'm trying to learn as much as I can grepping the source, but it's often easier to hear an explanation. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Datetime
Michael Glaesemann <[EMAIL PROTECTED]> writes: > I don't quite understand how I am wrong in saying that PostgreSQL does > not use UNIX epoch timestamps internally, as you've clearly explained > it doesn't. We are talking at cross-purposes. I thought you were suggesting that PG doesn't use a seconds-from-epoch form at all, but some other format (such as perhaps separate /mm/dd/hh/mm/ss fields). Sorry if I added to the confusion instead of dispelling it. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Datetime
On Aug 4, 2004, at 7:55 AM, Tom Lane wrote: Sorry if I added to the confusion instead of dispelling it. Not at all. I had no idea how timestamps are stored internally, but I do now. I just knew it wasn't UNIX epoch or the same as the text representation displayed in results. Your explanation has turned my negative "what it is not" knowledge into the much more positive "what it is", and that's always a good thing. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
