How to check date in postgresql

Question

my table name is tbl1. The fileds are id,name,txdate.

| ID | NAME    | TXDATE    |
| 1  | RAJ     | 1-1-2013  |
| 2  | RAVI    |           |
| 3  | PRABHU  | 25-3-2013 |
| 4  | SAT     |           |

Now i want to use select query for check txdate < 2-2-2013 in which rows have txdate not empty and the select also retrun which rows have txdate empty.

The Result is like this

| ID | NAME    | TXDATE    |
| 1  | RAJ     | 1-1-2013  |
| 2  | RAVI    |           |
| 4  | SAT     |           |

Any feasible solution is there?. With out using union it is possible?.

What is the data type of txdate?

Mahmoud Gamal
– Mahmoud Gamal

2013-04-10 06:58:22 +00:00
Commented Apr 10, 2013 at 6:58 — Mahmoud Gamal
– Mahmoud Gamal, Commented Apr 10, 2013 at 6:58
@MahmoudGamal txdate date.

Sathish
– Sathish

2013-04-10 07:08:23 +00:00
Commented Apr 10, 2013 at 7:08 — Sathish
– Sathish, Commented Apr 10, 2013 at 7:08

Mahmoud Gamal · Accepted Answer · 2013-04-10 07:02:44Z

2

Assuming that the TXDATE is of data type DATE then you can use WHERE "TXDATE" < '2013-2-2' OR "TXDATE" IS NULL. Something like:

SELECT *
FROM table1
WHERE "TXDATE" < '2013-2-2'
  OR "TXDATE" IS NULL;

See it in action:

SQL Fiddle Demo

answered Apr 10, 2013 at 7:02

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

Comments

Mahmoud Gamal · Accepted Answer · 2013-04-10 07:38:17Z

1

I don't now what database your are using and what data type the TXDATE is.

I just tried on my postgreSQL 9.2, with a field "timestamp without time zone".

I have three rows in the table , like:

 ac_device_name | ac_last_heartbeat_time

----------------+-------------------------

 Nest-Test1     |

 Nest-Test3     |

 Nest-Test2     | 2013-04-10 15:06:18.287

Then use below statement

select ac_device_name,ac_last_heartbeat_time 
from at_device 
where ac_last_heartbeat_time<'2013-04-11';

It is ok to return only one record:

 ac_device_name | ac_last_heartbeat_time

----------------+-------------------------

 Nest-Test2     | 2013-04-10 15:06:18.287

I think you can try statement like:

select * from tbl1 where TXDATE<'2-2-2013' and TXDATE is not NULL

this statement also works in my environment.

edited Apr 10, 2013 at 7:38

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

answered Apr 10, 2013 at 7:23

Flickerlight

9848 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to check date in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related