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.

You have a speciifc question: "How to create a datetime column with default value in SQLite". So by making your question title "Creating an SQL table with specific restrictions" you reduce your chances to get an answer by half — Alexey S. Larionov, Sep 26 '20 at 12:13

forpas · Accepted Answer · 2020-09-26T17:11:54.133

2

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.

edited Sep 26 '20 at 17:11

answered Sep 26 '20 at 17:06

forpas

160,666
10
38
76

But if I do it like this, when I output my database info in the console, I will get 1556399472 and not 2019-04-27 21:11:12. Is there a way I can convert it within the create statement? @forpas – Esmer Omer Sep 26 '20 at 17:29
But one of the restrictions is: *publication_date - needs to be able to store an integer (in UNIX system)*. So you must store it as an integer. When you query the table, you can use the DATETIME() function to return it formatted as YYYY-MM-DD hh:mm:ss. Isn't this that you want? – forpas Sep 26 '20 at 17:32
Somehow I still get a wrong answer on the platform which I am sending it on. I made sure I pay attention to all details so I don't have little mistakes. I will come with an explanation very soon – Esmer Omer Sep 26 '20 at 17:35
Nevermind... It was a little mistake, but now I remade it and it is correct. I appreciate the answer you gave me. – Esmer Omer Sep 26 '20 at 17:45

Gordon Linoff · Answer 2 · 2020-09-26T12:25:37.940

0

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.

edited Sep 26 '20 at 12:25

answered Sep 26 '20 at 12:13

Gordon Linoff

1,242,037
58
646
786

I don't think that there is a *built-in `datetime` datatype*, see https://www.sqlite.org/datatype3.html#date_and_time_datatype – Wolf Jul 18 '23 at 11:12
Switching from `int` to `datetime` doesn't change much, only the affinity is now `numeric` instead of `integer` and `'2019-04-27 21:11:12'` is stored as `text` in either case. – Wolf Jul 19 '23 at 19:02

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

2 Answers2