Datetime

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



---------------------------(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: 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 '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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Datetime

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: 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 '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


---------------------------(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: 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: 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: 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: Datetime

Michael Glaesemann 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: Datetime

On Aug 4, 2004, at 12:13 AM, Tom Lane wrote:

> Michael Glaesemann 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: Datetime

On Aug 4, 2004, at 12:13 AM, Tom Lane wrote:

> Michael Glaesemann 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: Datetime

Michael Glaesemann 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

---------------------------(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: 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