How to Get the Date and Time from Timestamp in Postgresql Select Query

How to get the date and time from timestamp in PostgreSQL select query?

There are plenty of date-time functions available with postgresql:

See the list here

http://www.postgresql.org/docs/9.1/static/functions-datetime.html

e.g.

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 16

For formatting you can use these:

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

e.g.

select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI') ...

how to select only by date from timestamp column in postgres?

You can use date() function

select * from audit_logs where date(created_date) = '2018-11-28'

query to fetch records between two date and time

Combine the two columns into a single timestamp by adding the time to the date:

select *
from some_table
where date_column + time_column 
         between timestamp '2017-06-14 17:30:00' and timestamp '2017-06-19 08:26:00';

Note that this will not use an index on date_column or time_column. You would need to create an index on that expression. Or better: use a single column defined as timestamp instead.

PostgreSQL: Convert timestamp to time - or retrieve only time from timestamp column

select timestamp '2014-04-03 12:34:00'::time

I prefer ANSI date/timestamp literals as they are shorter to write than to_timestamp()

The above is equivalent to:

select to_timestamp ('03.04.2014 12:34:00', 'DD.MM.YYYY HH24:MI:SS')::time

the ::time is Postgres' short hand notation for casting a value.

The following would be complete ANSI SQL if you want that:

select cast(timestamp '2014-04-03 12:34:00' as time)

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

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)

PostgreSQL 9.3: Check only time from timestamp

You can cast the column to a time: ColumnA::time

You should also not pass a time (or a date, or a timestamp) as a varchar. And you don't need dynamic SQL or a PL/pgSQL function for this:

CREATE OR REPLACE FUNCTION Function_Test(St_Time time, en_Time time)
RETURNS TABLE (columX timestamp)
AS
$BODY$
    SELECT * 
    FROM Test_Timestamp 
    where columna::time between st_time and en_time;
$BODY$
LANGUAGE sql;

Call it like this:

select *
from Function_Test(time '03:00:00', time '21:10:42');

getting date from timestamp in PostgreSQL

Cast it to date.

SELECT yourtimestamp::date;

If you need to extract other kinds of stuff, you might want to use EXTRACT or date_trunc

Both links are to the same page, were you'll find more date/time-related functions.

Postgres; select integers representing date and time query using to_timestamp between '2021-12-01 00:00:00' and '2021-12-01 23:59:59'

PostgreSQL simply doesn't know how to read the string you passed as parameter of the function. Try this:

SELECT to_timestamp('2021-12-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS') 

Response to EDIT1:

You cannot compare an integer between two timestamp. Try this:

to_timestamp(loggeddate) 
    between to_timestamp('2021-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and 
    to_timestamp('2021-12-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

---

Related Topics