0

I am trying to realize a project where people can login into a site where they find a personal calendar. In this calendar people shall be able to leave timestamps. Since a year has around 365 days, (and per day more than 1 timestamp is possible) there will be a lot of timestamps to save.

I need a way to save those timestamps in a sort of database. I am new to this and I want to know if using a JSON File for storing those timestamps or using a MySQL database is the better way of doing this.

Background-Story: I work on a project where a microcontroller does certain things at those given timestamps from the User. The user leaves timestamps in a calendar on an iOS-App. So it also has to be compatible with Swift/iOS.

Any ideas?

Kazuya91
  • 59
  • 7

2 Answers2

0

Databases have a few ways to store timestamps. For example the data-type TIMESTAMP or DATETIME are all ways to store timestamps

If you do it in a database, you have the ability to sync it across devices.

To do it in JSON, I'll refer you to this question on StackOverflow: The "right" JSON date format

EDIT: After reviewing the comments, you most likely want a database. I have an example here where you have a table for users and a table for events that can be joined to get each event for each user, even though each user has all their events in the same table.

I created this because I used to not know what Databases were good for, so I came here and someone put me in the right direction. Databases are VERY powerful and fast. To maintain everyone's JSON file of events would be a nightmare. I 100% recommend a database for your situation

Play around with this sample DB I created: http://sqlfiddle.com/#!9/523e2d/5

Chad K
  • 832
  • 1
  • 8
  • 19
  • Thanks. Didnt know that. But since i have many users with hundreds of timestamps, is it better to store a JSON file per user or is it good to create 1 Table for every user in a mySQL/SQL database? – Kazuya91 Sep 17 '17 at 17:46
  • Sorry for the late reply. What I would do is definitely store that in the database. That's exactly what they are for. Create 1 table of user where you store the name and Ids, then create 1 table for timestamps and the userId for the timestamp and then you can join the tables together – Chad K Sep 17 '17 at 20:10
  • @Kazuya91 Check out the edits I made. I hope this will help you. You want a database for your situation – Chad K Sep 18 '17 at 00:00
  • Tons of thanks for your effort! I will take a look at the DB you created. – Kazuya91 Sep 18 '17 at 11:21
0

If you have only a few users with a few timestamps and not much else going on, then you could indeed store each user’s data in a text file. That file could store JSON if you want, or XML, or more simply tab-delimited or comma-delimited text. If you just need the next timestamp, without keeping history, this might well be the best approach with one file per user. Beware of risks such as your own code inadvertently overwriting a file, or crashing during a write so the file is ruined and data lost.

A step-up from text-in-a-file is SQLite, to help you manage the data so that you need not do the chore of parsing. While SQLite is a valuable product when used appropriately, it is not meant to compete with serious database products.

But for more heavy-duty needs, such as much more data or multiple apps accessing the data, use a database. That is what they are for – storing large amounts of structured data in a way that can handle concurrent access by more than one user and more than one app.

While MySQL is more famous, for serious enterprise-quality database where preserving your data is critical, I recommend Postgres. Postgres has the best date-time handling of most any database, both in data types and in functions. Postgres also has the best documentation of any SQL database.

Another pair of options, both built in pure Java and both free-of-cost/open-source, are H2 and Derby. Or commercial products like Microsoft SQL Server or Oracle.

You have not really given enough details to make a specific recommendation. Furthermore, this site is not meant for software recommendations. For that, see the sister site: Software Recommendations Stack Exchange.

If you choose a database product, be sure to learn about date-time handling. Database products vary widely in this respect, and the SQL standard barely touches upon the subject. Quick tips: Store values in UTC, and indeed your database such as Postgres is likely to adjust incoming values. Apply a time zone only for presentation to users. Avoid the data-types that ignore the issue of time zone. So for standard SQL, this means using the TIMESTAMP WITH TIME ZONE data type rather than TIMESTAMP WITHOUT TIME ZONE. For Java work, use only the java.time classes rather than the troublesome old date-time classes, and for your purposes use the Instant and ZonedDateTime classes but not the LocalDateTime class which lacks any offset or time zone.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • Thank you very much for this detailed answer! Ok, then its settled. I am going to use a database. The tip with the datatypes with timezone is awesome. Whats is the advantage of using Postgres vs MySQL in THIS(!) project? – Kazuya91 Sep 18 '17 at 11:25
  • @Kazuya91 Postgres has the best date-time handling of most any database, both in [data types](https://www.postgresql.org/docs/current/static/datatype-datetime.html) and in [functions](https://www.postgresql.org/docs/current/static/functions-datetime.html). Postgres also has the best documentation of any SQL database. – Basil Bourque Sep 18 '17 at 14:27