CODEWITHSUNDEEP
sqlpostgresql
Abra Kadabraa
Abra Kadabraa

Reputation: 107

How to get start day timestamp and end day timestamp from value

For example I have one column TIMESTAMP WITH TIME ZONE with value '2017-07-09 19:43:54.418544' How to get from this value something like this :

2017-07-09 00:00:00 as start_day,
2017-07-09 23:59:59 as end_day

Upvotes: 1

Views: 6440

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

That format is not a timestamp with timezone. It doesn't have the timezone part. But with or without, you can use date_trunc():

select date_trunc('day', val) as start_day,
       (date_trunc('day', val) + interval '1 day' - interval '1 second') as end_day

I am not a fan of inclusive end-day bounds. I would recommend just saying "< the start of the next day".

Upvotes: 7

Related Questions