Inserting a specific date in UNIX format into a TABLE

238 Views Asked by At
CREATE TABLE IF NOT EXISTS posts (
 title VARCHAR ,
 post_description TEXT NOT NULL,
 time_posted TIMESTAMP,
 PRIMARY KEY(title)
);

UPDATE posts SET time_posted = ???? WHERE time_posted IS NULL;

I have a table that represents a post (on any social media you like), and that table contains the tile, the description, and the time that the post has been made.

In this example, if a post has a NULL date, I have to automatically modify it to a specific date, which is 27 April 2019 21:11:12 UTC (in UNIX format). The question is how can I do that? I have tried to find a way and read plenty of things on the internet.

I also saw a Unix Time Stamp - Epoch Converter and saw that the value for that date is 1556399472, but I am not sure how that would work in my table and I don't think it will convert correctly (I have tested it and did not work :< )

Another example here to make sure it is clear:

   The legendary flying stone | Fake news everyone | NULL

I must convert it to something like this

   The legendary flying stone | Fake news everyone | *The date I specified before which is UTC and must be in UNIX format*

NOTE: I am working in SQL in the IntelliJ IDE.

2

There are 2 best solutions below

2
On

You would do something like this:

update posts
    set time_posted = ?
    where time_posted is null;

If you are running the query through an application, then ? is the date you want. If you are running this manually, then you should format the date:

set time_posted = '2019-04-27 21:11:12 UTC'

The exact syntax of the string might depend on the database you are using.

0
On

The SQL DB uses timestamp as its datatype. This is innately different from the Unix TimeStamp(which is a number). You cannot store a Unix TimeStamp in a SQL Timestamp field. You can store the date that you want and then Cast of Convert it upon display or integration. You could create a conversion function to accomplish this and call that function when you need the SQL timestamp converted.