CODEWITHSUNDEEP
postgresql
Angel
Angel

Reputation: 79

How do I insert values when some of the columns are timestamps?

I have follwing SQL-statement:

CREATE TABLE account (
user_id serial primary key,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(50) NOT NULL,
email VARCHAR (355) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);

I want to insert my first row:

INSERT INTO account VALUES (
1, 'usernamehere', 'password123', '[email protected]');

What do I type as the values for the created_on and last_login timestamp column?

Upvotes: 0

Views: 2886

Answers (4)

user330315
user330315

Reputation:

There are different ways to specify a valid timestamp literal.

I prefer ANSI SQL literals, e.g.:

timestamp '2018-10-15 18:49:10'

which always uses the ISO timestamp format (that is yyyy-mm-dd and a 24 hour time notation).

Alternatively, in Postgres you can also cast a string that specifies the value in the ISO format to a timestamp: '2018-10-15 18:49:10'::timestamp

If you prefer other formats, you can use to_timestamp with a format mask, e.g:

to_timestamp('25.08.2018 21:34:10', 'dd.mm.yyyy hh24:mi:ss');

To get the current date and time you can use current_timestamp (which is standard SQL) or now() (which is Postgres specific).

Unrelated, but: you shouldn't provide a value for the user_id column as that is auto-generated and by specifying a value manually, you make the underlying sequence get out-of-sync with the inserted values.

So you should use:

INSERT INTO account 
  (username, password, email, created_on )
VALUES 
  ('usernamehere', 'password123', '[email protected]', current_timestamp);

Alternatively you could specify the user_id column, but use the default keyword instead:

INSERT INTO account 
  (user_id, username, password, email, created_on )
VALUES 
  (DEFAULT, 'usernamehere', 'password123', '[email protected]', current_timestamp);

Or if you want a specific value for created_on:

INSERT INTO account 
  (username, password, email, created_on )
VALUES 
  ('usernamehere', 'password123', '[email protected]', timestamp '2018-10-15 18:49:10');

Upvotes: 2

iLuvLogix
iLuvLogix

Reputation: 6420

I recommend that you read the official docs on date/time data-types first.

PostgreSQL accepts several input formats for timestamp with time zone or timestamp in general - the output format depends on the DateStyle parameter.

You can use Javascript's Date.now() function, divide the result by 1000 and feed it to PostgreSQL's to_timestamp(double precision) function.

That will always give you the correct value.

In your case:

INSERT INTO account (username, password, email, created_on, last_login) VALUES 
('usernamehere', 'password123', '[email protected]', now(), NULL);

Please NOTE: When inserting a new row, you don't supply the id - it's of type serial and is auto-generated in a sequence for you..

Upvotes: 1

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

INSERT INTO account VALUES (
1, 'usernamehere', 'password123', '[email protected]', now());

Last one can be null, if you wanna save that then pass it as now() as well

Upvotes: 0

cn0047
cn0047

Reputation: 17051

You can use function now(), like this:

INSERT INTO account VALUES (
1, 'usernamehere', 'password123', '[email protected]', now(), now());

In case last_login should remain NULL use this query:

INSERT INTO account VALUES (
1, 'usernamehere', 'password123', '[email protected]', now(), null);

Upvotes: 1

Related Questions