To: pgsql-interfaces@postgresql.org
From: "Luiz E. P. Fernandes" <lepfer@uol.com.br>
Subject: Seconds precision in timestamp columns
Message-ID: <187cc78d-fc34-aaa9-aeb0-f0045b9b4ce8@uol.com.br>
Date: Fri, 16 Jul 2021 16:03:53 -0300
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.12.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/187cc78d-fc34-aaa9-aeb0-f0045b9b4ce8%40uol.com.br>
Precedence: bulk

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