SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
for example
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
gives me
2008-09-22 00:00:00.000
Pros:
- No varchar<->datetime conversions required
- No need to think about locale
Grant usage/select to a single table
If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so:
GRANT CONNECT ON DATABASE mydb TO xxx;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO xxx;
GRANT SELECT ON mytable TO xxx;
Multiple tables/views (PostgreSQL 9.0+)
In the latest versions of PostgreSQL, you can grant permissions on all tables/views/etc in the schema using a single command rather than having to type them one by one:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;
This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO xxx;
Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don't pick up default privileges for all roles you're a member of when creating new objects... so there's still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow.
Multiple tables/views (PostgreSQL versions before 9.0)
To avoid errors in lengthy, multi-table changes, it is recommended to use the following 'automatic' process to generate the required GRANT SELECT
to each table/view:
SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');
This should output the relevant GRANT commands to GRANT SELECT on all tables, views, and sequences in public, for copy-n-paste love. Naturally, this will only be applied to tables that have already been created.
Best Answer
Inspired by @Frank's comment I ran some tests and adapted my query accordingly. This should be 1) correct and 2) as fast as possible:
As there are no future timestamps in your table (I assume), you need no upper bound.
date_trunc('day', now())
is almost the same asnow()::date
(or some other alternatives detailed below), only that it returnstimestamp
instead of adate
. Both result in atimestamp
anyway after adding aninterval
.Below expressions perform slightly differently. They yield subtly different results because
localtimestamp
returns data typetimestamp
whilenow()
returnstimestamp with time zone
. But when cast todate
, either is converted to the same local date, and atimestamp [without time zone]
is presumed to be in the local time zone, too. So when compared to the correspondingtimestamp with time zone
they all result in the same UTC timestamp internally. More details on time zone handling in this related question.Best of five. Tested with PostgreSQL 9.0. Repeated with 9.1.5: consistent results within 1 % error margin.
now()::date
is obviously slightly faster thanCURRENT_DATE
.