pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDatetime
11+ messages / 5 participants
[nested] [flat]
* Datetime
@ 2004-08-03 07:22 Pedro B. <pedro.borracha@netcabo.pt>
2004-08-03 10:23 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 10:56 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 10:58 ` Re: Datetime Devrim GUNDUZ <devrim@gunduz.org>
0 siblings, 3 replies; 11+ messages in thread
From: Pedro B. @ 2004-08-03 07:22 UTC (permalink / raw)
To: pgsql-sql
Hello,
I'm using a TIMESTAMP column with a now() default which (correctly i
assume) uses a 'yyyy-mm-dd hh:mm:ss' format.
Is it possible to make it something like 'yyyy-mm-dd hh:mm:ss:cc' ?
(basically, a DATE and a TIME, but with 2 decimal cases on the :cc and
not .ccccc as the TIME format.
Thanks,
\\pb
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
@ 2004-08-03 10:23 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 11:31 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2 siblings, 1 reply; 11+ messages in thread
From: Achilleus Mantzios @ 2004-08-03 10:23 UTC (permalink / raw)
To: Pedro B. <pedro.borracha@netcabo.pt>; +Cc: pgsql-sql
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 'yyyy-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 'yyyy-mm-dd hh:mm:ss:cc' ?
> (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and
> not .ccccc 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
2004-08-03 10:23 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-08-03 11:31 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 11:50 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Glaesemann @ 2004-08-03 11:31 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Pedro B. <pedro.borracha@netcabo.pt>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
2004-08-03 10:23 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 11:31 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
@ 2004-08-03 11:50 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 13:26 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 11+ messages in thread
From: Achilleus Mantzios @ 2004-08-03 11:50 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: Pedro B. <pedro.borracha@netcabo.pt>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
2004-08-03 10:23 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 11:31 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 11:50 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-08-03 13:26 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 15:13 ` Re: Datetime Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Glaesemann @ 2004-08-03 13:26 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Pedro B. <pedro.borracha@netcabo.pt>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
2004-08-03 10:23 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 11:31 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 11:50 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 13:26 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
@ 2004-08-03 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-03 22:16 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2004-08-03 15:13 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Pedro B. <pedro.borracha@netcabo.pt>; pgsql-sql
Michael Glaesemann <grzm@myrealbox.com> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
2004-08-03 10:23 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 11:31 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 11:50 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 13:26 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 15:13 ` Re: Datetime Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-03 22:16 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 22:55 ` Re: Datetime Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Glaesemann @ 2004-08-03 22:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pedro B. <pedro.borracha@netcabo.pt>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql
On Aug 4, 2004, at 12:13 AM, Tom Lane wrote:
> Michael Glaesemann <grzm@myrealbox.com> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
2004-08-03 10:23 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 11:31 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 11:50 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 13:26 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 15:13 ` Re: Datetime Tom Lane <tgl@sss.pgh.pa.us>
2004-08-03 22:16 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
@ 2004-08-03 22:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-04 01:32 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2004-08-03 22:55 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: Pedro B. <pedro.borracha@netcabo.pt>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql
Michael Glaesemann <grzm@myrealbox.com> 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 yyyy/mm/dd/hh/mm/ss fields). Sorry if I added
to the confusion instead of dispelling it.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
2004-08-03 10:23 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 11:31 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 11:50 ` Re: Datetime Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 13:26 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 15:13 ` Re: Datetime Tom Lane <tgl@sss.pgh.pa.us>
2004-08-03 22:16 ` Re: Datetime Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 22:55 ` Re: Datetime Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-04 01:32 ` Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 0 replies; 11+ messages in thread
From: Michael Glaesemann @ 2004-08-04 01:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pedro B. <pedro.borracha@netcabo.pt>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
@ 2004-08-03 10:56 ` Michael Glaesemann <grzm@myrealbox.com>
2 siblings, 0 replies; 11+ messages in thread
From: Michael Glaesemann @ 2004-08-03 10:56 UTC (permalink / raw)
To: Pedro B. <pedro.borracha@netcabo.pt>; +Cc: pgsql-sql
On Aug 3, 2004, at 4:22 PM, Pedro B. wrote:
> Is it possible to make it something like 'yyyy-mm-dd hh:mm:ss:cc' ?
> (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and
> not .ccccc 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Datetime
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
@ 2004-08-03 10:58 ` Devrim GUNDUZ <devrim@gunduz.org>
2 siblings, 0 replies; 11+ messages in thread
From: Devrim GUNDUZ @ 2004-08-03 10:58 UTC (permalink / raw)
To: Pedro B. <pedro.borracha@netcabo.pt>; +Cc: pgsql-sql
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 'yyyy-mm-dd hh:mm:ss' format.
>
> Is it possible to make it something like 'yyyy-mm-dd hh:mm:ss:cc' ?
> (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and
> not .ccccc 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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2004-08-04 01:32 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-03 07:22 Datetime Pedro B. <pedro.borracha@netcabo.pt>
2004-08-03 10:23 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 11:31 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 11:50 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-08-03 13:26 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-03 22:16 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 22:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-04 01:32 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 10:56 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-03 10:58 ` Devrim GUNDUZ <devrim@gunduz.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox