CODEWITHSUNDEEP
sqlpostgresqltimestamp
papski
papski

Reputation: 1281

Remove milliseconds in SQL commands

Having this kind of SQL Statement, how can I remove milliseconds from the 'date_created' column?

SELECT sales.id,
       sales.filename,
       account.description customer,
       sales.uploaded,
       sales.date_created
  FROM account
       JOIN salesStore sales
            ON sales.customer_id = account.id
 WHERE sales.date_created::date BETWEEN '2014-02-27' 
                                    AND '2014-03-01'
ORDER BY 
       sales.date_created DESC

Upvotes: 1

Views: 4417

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658242

The manual:

time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6 for the timestamp and interval types.

You can cast to timestamp(0) (or timestamptz(0)) to remove fractional seconds:

date_created::timestamp(0)

This rounds to full seconds - as opposed to date_trunc() which truncates.

Upvotes: 4

Juhi
Juhi

Reputation: 270

Try this. This will remove millisecond part from date.

DATEADD(ms, -DATEPART(ms, date), date)

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Try this

 DATE_TRUNC('second', sales.date_created);

See the docs for DATE_TRUNC.

postgresql doc

Upvotes: 7

Gorkk
Gorkk

Reputation: 1056

DATE_FORMAT(sales.date_created, '%Y-%m-%d') for MySQL.

For other sql engines it might be different and you will have to precise which you are using.

Edit: add %H:%i:%s to keep the date component as well.

Update

For PostgreSQL, see PostgreSQL's Documentation for Data Type Formatting Functions, or [SO] How to compare dates in datetime fields in Postgresql? which gives several options for date comparisons.

Upvotes: 2

Related Questions