0

I have a timestamp like 2014-08-17T06:16:55.967Z that I want to insert in a Postgres db, but I want to remove just the milliseconds and keep the 'T' and the 'Z'. Anyone know how that is possible? I've tried 2014-08-17T06:16:55.967Z::timestamp(0) or timestamptz(0), but they both take away what I want to keep.

I would like 2014-08-17T06:16:55Z.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
guy_without_a_name
  • 155
  • 1
  • 2
  • 13

1 Answers1

1

date_trunc

select date_trunc('second', '2014-08-17T06:16:55.967Z'::timestamp);
     date_trunc      
---------------------
 2014-08-17 06:16:55

http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

The T and Z in the input string are not saved in the timestamp column. If you want to show them then format the output

select to_char(
    date_trunc('second', '2014-08-17T06:16:55.967Z'::timestamp),
    'YYYY-MM-DD"T"HH24:MI:SSZ'
);
       to_char        
----------------------
 2014-08-17T06:16:55Z

http://www.postgresql.org/docs/current/static/functions-formatting.html

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260