Ask HN: Timestamp vs. millis, storing time in database

#1

Post by govindpatel » Thu, Jul 07, 2016, 10:49 AM UTC

Ok. this question usually come to my mind and always I prefer using millis because I think that is the easiest way to store and get the time in database.

But some of my friends prefer using timestamp.

So I just wanted to know, What is the best practise for storing time in database ?

When choosing between timestamp and millis which one you prefer using and why?

Re: Ask HN: Timestamp vs. millis, storing time in database

#2

Post by gjvc » Thu, Jul 07, 2016, 11:02 AM UTC

We have a very similar problem. We keep timestamps in numeric form as "epoch nanos" (nanoseconds from 1970-01-01 00:00) We are using PostgreSQL as the database, and I would very much like to use the native type (TIMESTAMP) [1], but PostgreSQL only keeps TIMESTAMP columns to microseconds, not nanoseconds. :-(

Using the native DBMS types allows use of native DBMS time and interval functionality, unless you like doing it yourself.

Re: Ask HN: Timestamp vs. millis, storing time in database

#3

Post by govindpatel » Thu, Jul 07, 2016, 1:34 PM UTC

gjvc wrote: post #2

We have a very similar problem. We keep timestamps in numeric form as "epoch nanos" (nanoseconds from 1970-01-01 00:00) We are using PostgreSQL as the database, and I would very much like to use the native type (TIMESTAMP) [1], but PostgreSQL only keeps TIMESTAMP columns to microseconds, not nanoseconds. :-( Using the native DBMS types allows use of native DBMS time and interval functionality, unless you like doing i…

we have some tables where we have timestamp and other places we have millis. We are also using postgreSQL database.

I have asked this question so that I can know whether any other person faces problem like this, it is kind of annoying when I see different ways of storing the time, but not knowing what is the best practise to save time in database.