0

We have a PostgreSQL database which we use as an intermediate staging area for our ETL pipelines. In the tables of this database, we have kept the time-related audit fields like creation time and modification time as integers.

I'm not exactly sure why but I think the idea was to make the join conditions simpler; in this case, simple integer comparison. As far as utility is concerned, we only use these timestamps for getting incremental deltas (like fetching records where modification_time >= last_update_time); we don't get values for these records from the source and, also, don't load them in the target.

I'm not sure if this is an acceptable design choice because whenever the topic of timestamps come up, more or less everybody says that timestamps should be kept as timestamps. I don't think that using timestamp instead of int will improve performance by any means. The only benefit I see is that the data would become easier to understand to anyone who wants to go through these audit fields.

Would it have been better if these audit fields were timestamps instead?

1 Answer 1

3

Probably. It is hard to tell from your story though. If the fields are only there to keep track of a sequence of events and an integer field is smaller than a date/time field and disk usage was of any concern at the time of design, it may have been a good decision.

Like you say no one ever really seems to care when and it is all about before and after. Perhaps it is the name of the field that makes you want it to be a date/time more than anything else.

1
  • Yeah, maybe. Whenever I hear someone complaining (at least in this case) about keeping timestamps as timestamps in the database, I ask for reasons as to why? But I only get opinions instead of some convincing answer. Thought I'll get some insights here. Thanks for responding. Aug 2, 2019 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.