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

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-

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

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

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

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

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-

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

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-

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

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]<ml-node+s1045698n5919421h53@n3.nabble.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')

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+s1045698n1843780h82@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.

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