Appendix A. Manipulating Date and Time Values

Because time values in focal sampling data record the time the observation was entered, which necessarily occurs after the observation is taken, it may be desirable to ignore the additional seconds. The views and functions that Babase supplies to do this are documented elsewhere. This appendix provides an introduction to the underlying PostgreSQL facilities supporting these sorts of operations.

The PostgreSQL date_trunc() function can be used to produce a time with the seconds forced to 0. Here is an example:

Example A.1. Using the Postgresql date_trunc() function to set seconds to zero


babase=> select date_trunc('minute', '23:15:52'::time);
 date_trunc
------------
 23:15:00
(1 row)


To obtain the portion of timestamp that the date_trunc() function discards, use the Babase date_mod() function, which defines date_trunc(period, daytime) as daytime - date_trunc(period, daytime).

Example A.2. Using the Babase date_mod() function to return the minutes and seconds.


babase=> select date_mod('hour', '23:15:52'::time);
 date_mod
------------
 00:15:52
(1 row)


The date_trunc() function produces a time, which is a suitable sort of value for further computation, the calculation of intervals, etc. To produce human readable text in the form HH:MM the PostgreSQL to_char() function may be used.

Example A.3. Using the Postgresql to_char() function to convert times to HH:MM text


babase=> select to_char('23:15:52'::time, 'HH24:MI');
 to_char
---------
 23:15
(1 row)


For further information computations which may be performed using dates and times see the PostgreSQL documentation on Date/Time Functions and Operators.


Page generated: 2024-03-06T15:02:42-05:00.