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?
