Discard millisecond part from timestamp

by

A cast to timestamp(0) or timestamptz(0) rounds to full seconds:

SELECT now()::timestamp(0);

Fractions are not stored in table columns of this type.

date_trunc() truncates (leaves seconds unchanged) – which is often what you really want:

SELECT date_trunc('second', now()::timestamp);

More Related Contents:

  1. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  2. Casting NULL type when updating multiple rows
  3. Double colon `::` notation in SQL
  4. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  5. How to convert PostgreSQL 9.4’s jsonb type to float
  6. Timestamp Difference In Hours for PostgreSQL
  7. Does PostgreSQL support “accent insensitive” collations?
  8. PostgreSQL: running count of rows for a query ‘by minute’
  9. Split comma separated column data into additional columns
  10. IN vs ANY operator in PostgreSQL
  11. How to delete duplicate entries?
  12. Escaping keyword-like column names in Postgres
  13. Transpose latest rows per user to columns
  14. Split column into multiple rows in Postgres
  15. Getting date list in a range in PostgreSQL
  16. Postgres Dynamic Query Function
  17. How do I delete a fixed number of rows with sorting in PostgreSQL?
  18. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  19. How to find a table having a specific column in postgresql
  20. postgresql: INSERT INTO … (SELECT * …)
  21. How to read the contents of an .sql file into an R script to run a query?
  22. PostgreSQL JOIN with array type with array elements order, how to implement?
  23. Trim trailing spaces with PostgreSQL
  24. Postgres error updating column data
  25. Check whether string is a date Postgresql
  26. Counting the number of occurrences of a substring within a string in PostgreSQL
  27. Converting String List into Int List in SQL
  28. PostgreSQL: Full Text Search – How to search partial words?
  29. Temporal database design, with a twist (live vs draft rows)
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment