public inbox for [email protected]  

help / color / mirror / Atom feed

Seconds precision in timestamp columns
2+ messages / 2 participants
[nested] [flat]

* Seconds precision in timestamp columns
@ 2021-07-16 19:03  Luiz E. P. Fernandes <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Luiz E. P. Fernandes @ 2021-07-16 19:03 UTC (permalink / raw)
  To: pgsql-interfaces

Hi,

In the dvdrental example database, there is a column named 
'payment_date' on the 'payment' table and columns 'rental_date' and 
'return_date' on the 'rental' table.  These columns appear on the 
information_schema.columns view with identical specifications: datatype 
= 'timestamp without timezone', datetime_precision = 6. However, a 
select on table 'payment' presents column 'payment_date' with 6 
fractional digits, while a select on table 'rental' presents columns 
'rental_date' and 'return_date' without fractional digits.

select payment_date from payment limit 1;
--> for example, payment_date: '2007-02-15 22:25:46.996577'

select rental_date, return_date from rental limit 1;
--> for example, rental_date: '2005-05-24 22:54:33'

This happens in PgAdmin and also in the query values returned by 
PQgetvalue(), with LibPq.
What defines this seconds precision?  Is it possible to find the number 
of fractional digits that will be returned in a datetime column, before 
calling PQgetvalue()?

Thanks in advance.

Luiz Fernandes







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Seconds precision in timestamp columns
@ 2021-07-17 14:04  Tom Lane <[email protected]>
  parent: Luiz E. P. Fernandes <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2021-07-17 14:04 UTC (permalink / raw)
  To: Luiz E. P. Fernandes <[email protected]>; +Cc: pgsql-interfaces

"Luiz E. P. Fernandes" <[email protected]> writes:
> In the dvdrental example database, there is a column named 
> 'payment_date' on the 'payment' table and columns 'rental_date' and 
> 'return_date' on the 'rental' table.  These columns appear on the 
> information_schema.columns view with identical specifications: datatype 
> = 'timestamp without timezone', datetime_precision = 6. However, a 
> select on table 'payment' presents column 'payment_date' with 6 
> fractional digits, while a select on table 'rental' presents columns 
> 'rental_date' and 'return_date' without fractional digits.

Presumably this is just an artifact of the example data.  timestamptz_out
suppresses trailing fractional zeroes, regardless of the column's nominal
precision.  So there's no reason to think that one column is being treated
differently from the others.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2021-07-17 14:04 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-16 19:03 Seconds precision in timestamp columns Luiz E. P. Fernandes <[email protected]>
2021-07-17 14:04 ` Tom Lane <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox