Allow default format for timestamps

[truthdoug]

Description

It'd be nice if I could set a default format for timestamp columns. For example, rather than seeing 2023-04-27 15:16:38.125568+00, I'd prefer to see 2023-04-27 15:16:38 (or maybe even without the seconds).

[j-bennet]

Hi @truthdoug, this might be a nice feature. I can't promise you when and if this would get implemented though, since everyone working on pgcli is contributing in their own free time. However, if you're interested in implementing the feature yourself, take a look at preprocessors in cli_helpers:

https://github.com/dbcli/cli_helpers/blob/main/cli_helpers/tabular_output/preprocessors.py

You would need to add a new preprocessor that would do the formating for date fields, and then reference that processor in whatever adapter you want it, certainly https://github.com/dbcli/cli_helpers/blob/main/cli_helpers/tabular_output/tabulate_adapter.py, but maybe the others too, like the delimited output and tsv.

We are happy to accept contributions, and it might get you the feature quicker.

[dbaty]

I would find it very unexpected to see two distinct values (e.g. "2023-01-01 00:00:00.12345" and "2023-01-01 00:00:00.67890") both appear the same, as "2023-01-01 00:00:00". I guess that there may be some use cases where the timestamps are such that having millisecond- or second-scale precision is irrelevant, but that seems very specific.

Also, PostgreSQL function date_trunc already lets us print a date with any precision:

> select now(), date_trunc('second', now())
+-------------------------------+------------------------+
| now                           | date_trunc             |
|-------------------------------+------------------------|
| 2024-03-05 09:41:17.807009+00 | 2024-03-05 09:41:17+00 |
+-------------------------------+------------------------+

I am not sure that we really want to introduce a new format in pgcli for such rare use cases.

[truthdoug]

Yeah, I agree that would be jarring.... but that's why it'd be configurable. For the data I'm looking at, the columns are general significant to the day and possibly the minute. The seconds and microseconds never matter.

I'm aware of date_trunc and other PG functions that allow me to reformat dates. I proposed this as a feature that I thought might be compatible with how pgcli helps with better visuals.

I may take a stab of this if I find some of that "free time" I hear other people talking about.

I think my dream goal would be to have default formatting by column name so that I would get an expected format on any query. Something like:

[date formats]
birthdate = 'YYYY-MM-DD'
registration = 'YYYY-MM-DD HH24:MI'
graduation_year = 'YYYY'

[j-bennet]

I may take a stab of this if I find some of that "free time" I hear other people talking about.

I see you have kids, @truthdoug. 😂

[j-bennet]

@dbaty There's no harm in making this feature configurable. When it's configured to something non-default though, it would be good to have a visual indication of it (for example in the status bar). I can imagine the user configuring it to truncate timestamps to date, forgetting about it, and then being alarmed when all timestamps look the same.