postgresql ignore milliseconds from timestamp when doing a select statement

Question

I have TableA and it has a field of time_captured | timestamp without time zone | default now() It is being used to record when data was inserted into the table. However, I am trying to do a select and ignore milliseconds. So instead of having

2015-07-11 18:06:33.690498
2015-07-12 13:36:21.274509
2015-07-12 13:36:24.222577
2015-07-12 13:36:26.515593

I would like to have

2015-07-11 18:06:33
2015-07-12 13:36:21
2015-07-12 13:36:24
2015-07-12 13:36:26

Having the microseconds in the table is needed but what I do not need is for the output to show the microseconds. How can I do this ? The closest I have come is by using the following but I am not able to figure out how to pull the full date with the time as shown above.

SELECT EXTRACT(YEAR FROM time_captured  AT TIME ZONE 'UTC') FROM server_perf;   date_part

Thank you :)

Ned Rockson · Accepted Answer · 2015-07-14 00:50:47Z

18

You can use the date_trunc function to achieve this:

select date_trunc('second', time_captured) from server_perf;

see http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC for more on this.

answered Jul 14, 2015 at 0:50

Ned Rockson

1,1757 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql ignore milliseconds from timestamp when doing a select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related