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

Question

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:

• title - needs to be able to store letters, digits, and special characters
• news_description - needs to be able to store the text of the newsletter
• publication_date - needs to be able to store an integer (in UNIX system)

In addition to that, we have the following restrictions:

• the table will only be created if the table does not exist already

• the primary key will be the title

• 2 newsletters can't have the same description

• the news_description can not be null

• in case the publication_date is not specified I need to set it to 27 April 2019 21:11:12 using UTC and the date must be in UNIX format

    CREATE TABLE IF NOT EXISTS posts (
       title VARCHAR,
       news_description TEXT UNIQUE NOT NULL,
       publication_date "I don't know what I should write here in order to respect the last restriction"
       PRIMARY KEY(title)
    );
  

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.

Answer 1

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.

Answer 2

As far as I know, Unix format means Unix epoch format which is the number of seconds since 1970-01-01. This would be represented as a number:

publication_date int default 1556399472

That said, I would really recommend using the built-in datetime datatype:

publication_date datetime default '2019-04-27 21:11:12'

It is easy enough to convert to a Unix epoch when needed.