Postgresql | How to save default timestamp in unix format instead of yyyy-dd-mm hh:mm:ss

Author - Deepika
18.04.2017
|
0 Comments
|

In Postgresql we can save the default timestamp with now() method:

CREATE TABLE test
(
id serial NOT NULL,
name character varying(255),
last_modified_time timestamp without time zone DEFAULT now()
)

This will save the date timestamp in this format:

1

If we want to use the last_modified_time in code we usually convert it in unix time format for easy and faster comparison. We can speed up this process by saving default timestamp in unix format in the database:

CREATE TABLE test
(
id serial NOT NULL,
name character varying(255),
last_modified_time bigint NOT NULL DEFAULT (date_part('epoch'::text, now()) * (1000)::double precision)
)

The date_part and epoch commands helps in storing the date timestamp in unix form:

date_part('epoch'::text, now()) * (1000)::double precision

date_part function helps in extracting the date parts and epoch helps in converting the current timestamp into seconds(unix).

The following screenshot displays the updated time format:

2

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published. Required fields are marked *