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

Question

I have a table.

as you can see created_date column is timestamp field. now on selection i want to consider only date value. for e.g if i want to make selection of rows from today i want to do something like:-

select * from audit_logs where created_date = '2018-11-28';

the above query returns null. is it possible to make selection this way ?

I can't read that tiny image text... Please, use formatted text instead of images the next time. — jarlh
– jarlh, Commented Nov 28, 2018 at 6:21

Fahmi · Accepted Answer · 2018-11-28 05:58:25Z

16

You can use date() function

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

answered Nov 28, 2018 at 5:58

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

3 Comments

sagar limbu Over a year ago

thanks. i didn't knew about this date() function. it is working.

sagar limbu Over a year ago

yes sure, it asks to wait for some minutes before i can accept it.

user330315 Over a year ago

@sagarlimbu: it's not really a function. It's a different way to write a type cast. It's the same as created_date::date or cast(created_data as date)

Joachim Nielandt · Accepted Answer · 2018-11-28 07:23:27Z

0

An alternative function is date_trunc(text, timestamp), if you want more flexibility (truncate to hour, for example). Check out the postgres documentation (which is great) for all the date/time functions: https://www.postgresql.org/docs/current/functions-datetime.html

You do get a timestamp back instead of date, mind.

answered Nov 28, 2018 at 7:23

Joachim Nielandt

1551 silver badge10 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related