sqlsqlitesqlitejdbc

How can I convert an int (UNIX timestamp) to datetime in SQLite?


I have to create a table in SQL(I am using IntelliJ and Java), and the table must respect the following restrictions.

The table will contain the following columns:

In addition to that, we have the following restrictions:

So my question is how can I respect all of the restriction I have said above all while I create the table

NOTE: This is homework I have and I can't add anything besides the table there, so I need to respect all of the restrictions within the creation of the table.


Solution

  • The data type of publication_date must be INTEGER and the default value for this column must be 1556399472 which is the equivalent timestamp for 27 April 2019 21:11:12.
    There is no problem to store dates as integers in SQLite because it is easy to retrieve a readable date by using the 'unixepoch' modifier with the Date And Time Functions of SQLite.
    For example:

    SELECT DATETIME(1556399472, 'unixepoch')
    

    returns:

    2019-04-27 21:11:12
    

    Also there is no data type VARCHAR in SQLite. You should use TEXT.

    So the create statement of your table can be:

    CREATE TABLE IF NOT EXISTS posts (
      title TEXT,
      news_description TEXT UNIQUE NOT NULL,
      publication_date INTEGER NOT NULL DEFAULT 1556399472,
      PRIMARY KEY(title)
    );
    

    You may remove NOT NULL from the definition of publication_date if you want it to be nullable.