database date time format

chxxangie · #1 (**permalink**) 11-23-07, 01:38

2007-11-23 11:10:37.865588+08

what is the meaning of behind part ".865588+08" ?
and how to set it???

shammat · #2 (**permalink**) 11-23-07, 04:31

As those are microseconds I assume that this is simply the scientific notation for the number of microseconds.
So you have .865588 * 10^8 microseconds which computes to 86558800.

Check out the comments about microseconds in the manual

Quote:

Originally Posted by manual

The seconds field, including fractional parts, multiplied by 1 000 000. Note that this includes full seconds.

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Result: 28500000

amthomas · #3 (**permalink**) 11-26-07, 13:21

from docs:

Quote:

TIMESTAMP '2004-10-19 10:23:54'

is a timestamp without time zone, while

TIMESTAMP '2004-10-19 10:23:54+02'

is a timestamp with time zone. PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type:

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

http://www.postgresql.org/docs/8.2/s...-datetime.html