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

[Patrick B]

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

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

[amulsul]

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] 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')

but it doesn't work.. I get 0 rows... what am I doing wrong?
cheersPatrick  
 
   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 
ml-node+s1045698n1843780...@n3.nabble.com 
 To unsubscribe from PostgreSQL - general, click here.
 NAML   




--
View this message in context: 
http://postgresql.nabble.com/Get-date-timestamp-3-without-time-zone-column-PGSQL-9-5-tp5919421p5919430.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

[Lucas Possamai]

2016-09-05 15:17 GMT+12:00 Patrick B :

>
>> You might want to share the version of PostgreSQL you are using.
>>
>> You might want to try date_trunc and AT TIMEZONE function/operators-
>>
>> https://www.postgresql.org/docs/9.4/static/functions-datetim
>> e.html#FUNCTIONS-DATETIME-TRUNC
>>
>> 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: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[Pavel Stehule]

2016-09-05 5:17 GMT+02:00 Patrick B :

>
>> You might want to share the version of PostgreSQL you are using.
>>
>> You might want to try date_trunc and AT TIMEZONE function/operators-
>>
>> https://www.postgresql.org/docs/9.4/static/functions-datetim
>> e.html#FUNCTIONS-DATETIME-TRUNC
>>
>> 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: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[Patrick B]

>
>
> You might want to share the version of PostgreSQL you are using.
>
> You might want to try date_trunc and AT TIMEZONE function/operators-
>
> https://www.postgresql.org/docs/9.4/static/functions-
> datetime.html#FUNCTIONS-DATETIME-TRUNC
>
> 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: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

[Tom Lane]

Patrick B  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Sameer Kumar]

On Mon, Sep 5, 2016 at 10:59 AM Patrick B  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-

https://www.postgresql.org/docs/9.4/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

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