CODEWITHSUNDEEP
jsonpostgresqlsql-updatejsonb
T Anna
T Anna

Reputation: 1004

How to update json object in Postgres for current time in epoch

I need to update a json object for the 'lastUpdated' field with the current time in Epoch. I am doing the following but it is failing:

UPDATE data SET data = data  || '{"lastUpdated": extract(epoch from current_timestamp) }'
 WHERE dtype='Employee' and data->>'id' = '1234';

The following itself doesn't work:

UPDATE data SET data = data  || '{"lastUpdated": current_timestamp }'
 WHERE dtype='Employee' and data->>'id' = '1234';

I am getting 'invalid input syntax for type json'.

Please note, the following works when i set a custom epoch time:

UPDATE data SET data = data  || '{"dateAdded":1447502107000 , "lastUpdated":1447502107000}' WHERE dtype='Employee' and data->>'id' = '1234';

Upvotes: 1

Views: 1736

Answers (2)

klin
klin

Reputation: 121644

First, you should alter the data type of the column data to jsonb, as the concatenation operator || does not work with json. 

alter table data alter data type jsonb;

Then use the function jsonb_build_object():

UPDATE data SET data = data || jsonb_build_object('lastUpdated', extract(epoch from current_timestamp))
WHERE dtype='Employee' and data->>'id' = '1234';

If you do not want to change the column type, you have to use casting:

UPDATE data SET data = data::jsonb || jsonb_build_object('lastUpdated', extract(epoch from current_timestamp))
WHERE dtype='Employee' and data->>'id' = '1234';

Update

Choose one of the two variants to round or get rid of precision of timestamp:

select
    extract(epoch from current_timestamp),
    replace(extract(epoch from current_timestamp::timestamp(3))::text, '.', ''),
    concat(extract(epoch from current_timestamp::timestamp(0))::text, '000')

    date_part     |    replace    |    concat     
------------------+---------------+---------------
 1533560841.14562 | 1533568041146 | 1533568041000
(1 row)

Alternatively:

select
    extract(epoch from current_timestamp),
    extract(epoch from current_timestamp(3))* 1000 as v1,
    extract(epoch from current_timestamp(0))* 1000 as v2

Upvotes: 2

Shivam Batra
Shivam Batra

Reputation: 68

This is not working because extract is a function and by using it inside '', you are basically sending a string, which returns you timestamp

UPDATE data SET data = data  || '{"lastUpdated": extract(epoch from 
current_timestamp) }'
WHERE dtype='Employee' and data->>'id' = '1234';

Meanwhile this works because you are storing the way it is meant to store

UPDATE data SET data = data  || '{"dateAdded":1447502107000 , 
"lastUpdated":1447502107000}' WHERE dtype='Employee' and data->>'id' = '1234';

Though I would say to try Klin's way, but the solution to what you are asking can also be (make sure you change the data type to JSONB):

UPDATE data SET data = data  || '{"lastUpdated":' || extract(epoch from 
current_timestamp)::text || '}'
WHERE dtype='Employee' and data->>'id' = '1234';

Upvotes: 0

Related Questions