0

I'm working on a social network website and I'm not quite sure when I should store the date/datetime. The service includes some of the following events:

  • Register/Login
  • Follow
  • "Like" (images & comments)
  • Post a comment
  • Post an image

In my eyes, I would use DateTime for all of them... Although I'm not sure if that's professional or maybe it's just a waste of space.

When should I store the time of an event? Thanks in advance.

5
  • 1
    Not to sound glib, but what you store depends on your application/reporting needs. If you don't need the time component, use Date. If you do, use DateTime.
    – Oded
    Commented Feb 2, 2014 at 18:29
  • @Oded In the short run, I don't really need anything for most of them. However, I don't know about the long run and I'm asking what suggestions people who have experience with social networks have.
    – Shahar
    Commented Feb 2, 2014 at 18:43
  • 2
    You need to ask the business about the reporting needs and figure out the functional requirements (say, if you want to throttle log in attempts - you will need to store something that has the right time resolution).
    – Oded
    Commented Feb 2, 2014 at 18:46
  • Why do you have to make that decision now? If you get a lot of feedback pertaining to needing to know the time, then you can add it. At this point, anything you can leave out will help with performance.
    – JeffO
    Commented Feb 3, 2014 at 2:16
  • If it's an event/a point in time user DateTime (in UTC). Thanks to timezones there is no good mapping between points in time and dates, so I'd avoid dates whenever possible. Commented Feb 3, 2014 at 12:39

4 Answers 4

1

For all the things that you have mentioned I would store a date/time because they are things that actually took place at a certain time and you can easily capture that time automatically.

Capturing the time allows you more flexibility than just capturing a date does. For instance, you could show the user the Time for things which happened in the last 0-48 hours as well as adjust the time for their local timezone. For things that happened a longer time ago you could then choose to show them the date even though you are storing the entire time. For things like login, I would probably never show the user the Time they registered, but there's no compelling reason not to have that piece of data.

I tend to store Dates for things which time is not necessary and/or users have to enter manually because entering times manually takes longer and half the time you will have guesses anyway.

0

I would not necessarily store a Datetime for all of those. However in most cases it seems to be a good idea. Here's my logic:

  • Register/Login: It's a no-brainer to store it, here. Just think of support-tickets concerning hacked accounts or the case when you block someones account? You'll want to be able to tell a customer when it happened.
  • Following, Likes: To me there is no good reason that would make it necessary to store this. These fields are personal interest based and unless you're planning statistics on these fields that take time into account, you can let it go, here. I would go as far as saying that these are just metainformation. Follows being metainformation for an account and likes metainformation for a post.
  • Posting Comments/Images however are real information. The perspective the user has towards this information will change over time. No user comments a "It's my birthday, today" post half a year later wishing them a nice birthday. While after this long a timespan the exact time of the most might not matter anymore for this post, might, for other posts. So logically storing a Datetime seems important to me, here.
3
  • I was thinking for likes for things like finding trending comments/images/etc.
    – Shahar
    Commented Feb 2, 2014 at 19:15
  • Then of course Timestamps will start to matter. However another thing that just came to mind is schema... Constructing a schema around likes and Follows that scales well could become quite hard I assume.
    – juwi
    Commented Feb 2, 2014 at 19:23
  • You could, instead of storing all likes together with a timestamp construct a seperate score for calculating trending topics and such with the score being based on the datetime of the corresponding post and activity over time.
    – juwi
    Commented Feb 2, 2014 at 19:28
0

You are recording the moment in time when some user action occurred. This is commonly called a timestamp. Unfortunately, storing timestamps in databases is more complicated than most people realize, and the correct solution depends on the particular database implementation you are using.

You want to record the moment an event occurred, in this case some user action. There are two common ways to represent a moment in time: as a local time together with a timezone offset from Greenwich Mean Time, or as a number of seconds (or milliseconds or nanoseconds) since some moment in the past (start of epoch). Unfortunately, no generally support SQL type does either of these. The DATE and DATETIME types do not record a timezone, and few databases have any type that is represented as "time since start of epoch". The SQL standard defines a type 'TIMESTAMP WITH TIMEZONE' but it is few popular databases support it.

Most developers compromise by using DATETIME with some implied timezone. If you do this, you will have to be careful to record all times in the same timezone. The only one that makes sense is to record all times in GMT. Set the default server timezone to GMT and set the database timezone to GMT if applicable. You must be very careful to correctly convert application data types to the proper timezone before recording them in the database.

Alternatively, you can record timestamps as a numeric offset since some point in the past. The Unix system and it's derivatives, and the Java runtime represent time as seconds since 1970-01-01 00:00 GMT. If you do this you may want to define views that convert these to some readable representation.

0

General rule of thumb when talking about your primary data store; tend towards storing more information rather than less.

There are good reasons why you might want to know a date & time for all those events, as others have pointed out. If not now then later.

In all those specific cases you list, I would store a date and time.

And really, adding a date and time field should only have a tiny impact on performance. If your doing an INSERT anyway, what's one extra field? You can start micro-optomising performance later if you need to.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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