Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

by

You can cast your timestamp to a date by suffixing it with ::date. Here, in psql, is a timestamp:

# select '2010-01-01 12:00:00'::timestamp;
      timestamp      
---------------------
 2010-01-01 12:00:00

Now we’ll cast it to a date:

wconrad=# select '2010-01-01 12:00:00'::timestamp::date;
    date    
------------
 2010-01-01

On the other hand you can use date_trunc function. The difference between them is that the latter returns the same data type like timestamptz keeping your time zone intact (if you need it).

=> select date_trunc('day', now());
       date_trunc
------------------------
 2015-12-15 00:00:00+02
(1 row)

More Related Contents:

  1. Discard millisecond part from timestamp
  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. Transpose latest rows per user to columns
  13. Split column into multiple rows in Postgres
  14. Compute percents from SUM() in the same SELECT sql query
  15. SQL Between clause with strings columns
  16. INSERT rows into multiple tables in a single query, selecting from an involved table
  17. Best way to delete millions of rows by ID
  18. How to find a table having a specific column in postgresql
  19. postgresql: INSERT INTO … (SELECT * …)
  20. How to read the contents of an .sql file into an R script to run a query?
  21. PostgreSQL JOIN with array type with array elements order, how to implement?
  22. Trim trailing spaces with PostgreSQL
  23. Return multiple columns and rows from a function PostgreSQL instead of record
  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 query between date ranges
  29. Why isn’t row level security enabled for Postgres views?
  30. Is there a postgres CLOSEST operator?

Leave a Comment