Postgres Pro
Downloads
Home   >   mailing lists

Thread: Get date timestamp(3) without time zone column - PGSQL 9.5

Get date timestamp(3) without time zone column - PGSQL 9.5

From
Patrick B
Date:
Hi guys,

I got the tasks table that has the tasks_start column:
tasks_start        | timestamp(3) without time zone 


select tasks_start from tasks LIMIT 1;
tasks_start           
------------------- 
2016-08-10 00:30:00 

 I'm trying to cast the date, using this query:
SELECT cast(tasks_start as date) FROM "jobs" WHERE "tasks"."deleted" = 'f' AND "tasks"."recurrence_id" = 1 AND (Date(tasks_start) in ('2016-08-10')

but it doesn't work.. I get 0 rows... what am I doing wrong?

cheers
Patrick 

Re: Get date timestamp(3) without time zone column - PGSQL 9.5

From
Sameer Kumar
Date:


On Mon, Sep 5, 2016 at 10:59 AM Patrick B <patrickbakerbr@gmail.com> wrote:
Hi guys,

I got the tasks table that has the tasks_start column:
tasks_start        | timestamp(3) without time zone 


select tasks_start from tasks LIMIT 1;
tasks_start           
------------------- 
2016-08-10 00:30:00 

 I'm trying to cast the date, using this query:
SELECT cast(tasks_start as date) FROM "jobs" WHERE "tasks"."deleted" = 'f' AND "tasks"."recurrence_id" = 1 AND (Date(tasks_start) in ('2016-08-10')

You might want to share the version of PostgreSQL you are using. 

You might want to try date_trunc and AT TIMEZONE function/operators-


SELECT date_trunc('day', tasks_start at TIME ZONE 'EST')

Note: I have not tried this statement
 
Is this something you are going to use often? If that is the case then consider to re-model your query. The moment you use an expression on a column it would not use a normal BTree index.


but it doesn't work.. I get 0 rows... what am I doing wrong?

cheers
Patrick 
--
--
Best Regards
Sameer Kumar | DB Solution Architect 
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 

Skype: sameer.ashnik | www.ashnik.com

Re: Get date timestamp(3) without time zone column - PGSQL 9.5

From
Tom Lane
Date:
Patrick B <patrickbakerbr@gmail.com> writes:
>  I'm trying to cast the date, using this query:
>> SELECT cast(tasks_start as date) FROM "jobs" WHERE "tasks"."deleted" = 'f'
>> AND "tasks"."recurrence_id" = 1 AND (Date(tasks_start) in ('2016-08-10')
> but it doesn't work.. I get 0 rows... what am I doing wrong?

Are you sure you're not getting an error?  The query is specifying fields
in "tasks" but the FROM clause only lists "jobs".

Either one of those two cast-to-date syntaxes should work, so your problem
is somewhere else.

            regards, tom lane

Re: Get date timestamp(3) without time zone column - PGSQL 9.5

From
Patrick B
Date:

You might want to share the version of PostgreSQL you are using. 

You might want to try date_trunc and AT TIMEZONE function/operators-


SELECT date_trunc('day', tasks_start at TIME ZONE 'EST')

Note: I have not tried this statement
 
Is this something you are going to use often? If that is the case then consider to re-model your query. The moment you use an expression on a column it would not use a normal BTree index.


Hmm... I see....


select date_trunc('day', TIMESTAMP '2016-08-10') FROM tasks

 And I get: 

2016-08-10 00:00:00


I actually need just the date 2016-08-10, without 00:00:00...
Any idea?

Cheers
Patrick

Re: Get date timestamp(3) without time zone column - PGSQL 9.5

From
Pavel Stehule
Date:


2016-09-05 5:17 GMT+02:00 Patrick B <patrickbakerbr@gmail.com>:

You might want to share the version of PostgreSQL you are using. 

You might want to try date_trunc and AT TIMEZONE function/operators-


SELECT date_trunc('day', tasks_start at TIME ZONE 'EST')

Note: I have not tried this statement
 
Is this something you are going to use often? If that is the case then consider to re-model your query. The moment you use an expression on a column it would not use a normal BTree index.


Hmm... I see....


select date_trunc('day', TIMESTAMP '2016-08-10') FROM tasks

 And I get: 

2016-08-10 00:00:00


I actually need just the date 2016-08-10, without 00:00:00...

cast to date.

PostgreSQL timestamp shows time always.

Regards

Pavel
 
Any idea?

Cheers
Patrick

Re: Get date timestamp(3) without time zone column - PGSQL 9.5

From
Lucas Possamai
Date:


2016-09-05 15:17 GMT+12:00 Patrick B <patrickbakerbr@gmail.com>:

You might want to share the version of PostgreSQL you are using. 

You might want to try date_trunc and AT TIMEZONE function/operators-


SELECT date_trunc('day', tasks_start at TIME ZONE 'EST')

Note: I have not tried this statement
 
Is this something you are going to use often? If that is the case then consider to re-model your query. The moment you use an expression on a column it would not use a normal BTree index.


Hmm... I see....


select date_trunc('day', TIMESTAMP '2016-08-10') FROM tasks

 And I get: 

2016-08-10 00:00:00


I actually need just the date 2016-08-10, without 00:00:00...
Any idea?

Cheers
Patrick


I agree with @Tom, your first sql should work... Based on the table name "tasks" you provided, try this:


SELECT cast(jtasks_start as date) FROM "tasks" WHERE (date(tasks_start) in ('2016-08-11')) 


Lucas 

Re: Get date timestamp(3) without time zone column - PGSQL 9.5

From
amulsul
Date:
Try using double colon opperator instead of cast().  

E. g.  task_start::date

Regards,
Amul
----------------------------------------------------------------------------------------------------
Sent from a mobile device. Please excuse brevity and tpyos.

On Mon, 5 Sep, 2016 at 8:29 am, Patrick Baker [via PostgreSQL]
Hi guys,

I got the tasks table that has the tasks_start column:
tasks_start        | timestamp(3) without time zone 


select tasks_start from tasks LIMIT 1;
tasks_start           
------------------- 
2016-08-10 00:30:00 

 I'm trying to cast the date, using this query:
SELECT cast(tasks_start as date) FROM "jobs" WHERE "tasks"."deleted" = 'f' AND "tasks"."recurrence_id" = 1 AND (Date(tasks_start) in ('2016-08-10')

but it doesn't work.. I get 0 rows... what am I doing wrong?

cheers
Patrick 


If you reply to this email, your message will be added to the discussion below:
http://postgresql.nabble.com/Get-date-timestamp-3-without-time-zone-column-PGSQL-9-5-tp5919421.html
To start a new topic under PostgreSQL - general, email [hidden email]
To unsubscribe from PostgreSQL - general, click here.
NAML

View this message in context: Re: Get date timestamp(3) without time zone column - PGSQL 9.5
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Get date timestamp(3) without time zone column - PGSQL 9.5

From
Patrick B
Date:
Hi guys,

You were right, there was something wrong with my original query:

SELECT cast(tasks_start as date) FROM "tasks" WHERE (Date(tasks_start) in ('2016-08-10');

I was able to get the expected data using the above query...

Cheers
Patrick