Format timestamp to extract just the date string

A beginner questions on formatting timestamp columns.

How does one extract just the date from the timestamp column.

For example, I have a date column defined as a Timestamp datatype

+-----------+-----------+-------+----------------+
|   Field   |   Type    | Null  |    Default     |
+-----------+-----------+-------+----------------+
| id        | INT       | false | unique_rowid() |
| date      | TIMESTAMP | true  | NULL           |
| caller    | STRING    | true  | NULL           |
| component | STRING    | true  | NULL           |
| method    | STRING    | true  | NULL           |
| url       | STRING    | true  | NULL           |
| count     | INT       | true  | NULL           |
| min       | INT       | true  | NULL           |
| median    | INT       | true  | NULL           |
| perc90    | INT       | true  | NULL           |
| perc95    | INT       | true  | NULL           |
| perc96    | INT       | true  | NULL           |
| perc97    | INT       | true  | NULL           |
| perc98    | INT       | true  | NULL           |
| perc99    | INT       | true  | NULL           |
| max       | INT       | true  | NULL           |
| status    | INT       | true  | NULL           |
+-----------+-----------+-------+----------------+

I want to extract only the date part from the timestamp when I run sql command from the cockroach sql command line utility.

Basically I only want to display the date from the result of the following query

select distinct min(date), max(date) , caller, component from api group by caller, component having max(date) < TIMESTAMP '2016-10-01' order by max(date);
+---------------------------------+---------------------------------+---------------------+-------------------------+
|            min(date)            |            max(date)            |       caller        |        component        |
+---------------------------------+---------------------------------+---------------------+-------------------------+
| 2015-09-11 00:00:00 +0000 +0000 | 2015-10-01 00:00:00 +0000 +0000 | dps-services        | dfmservices             |
| 2015-10-20 00:00:00 +0000 +0000 | 2015-10-28 00:00:00 +0000 +0000 | consumer-controller | vmanage-test-agent      |
| 2016-02-02 00:00:00 +0000 +0000 | 2016-03-12 00:00:00 +0000 +0000 | consumer-controller | websrv_commerce         |
| 2015-09-11 00:00:00 +0000 +0000 | 2016-03-14 00:00:00 +0000 +0000 | cbp-web             | rsrv_CDISI_VTSTKNREGSTN |
| 2015-09-11 00:00:00 +0000 +0000 | 2016-03-14 00:00:00 +0000 +0000 | cbp-web             | rsrv_CDISI_VTSTRANHIST  |
| 2016-02-04 00:00:00 +0000 +0000 | 2016-04-06 00:00:00 +0000 +0000 | ddtservices         | THM-Outbound            |
| 2015-09-11 00:00:00 +0000 +0000 | 2016-04-06 00:00:00 +0000 +0000 | cbp-web             | eag                     |
| 2016-01-27 00:00:00 +0000 +0000 | 2016-04-06 00:00:00 +0000 +0000 | dfmservices         | rsrv_CDISI_MRCHSRCH_TC  |
| 2016-04-06 00:00:00 +0000 +0000 | 2016-05-06 00:00:00 +0000 +0000 | profile-services    | pips                    |

not sure about PostGres, but in MySQL, you simple wrap the column result in date()

try this:

select distinct date(min(date)) as min, date(max(date)) as max , caller, component from api group by caller, component having max(date) < TIMESTAMP '2016-10-01' order by max(date);

Hi youngl,
you can extract the date part from a statement using a cast to the DATE type: X::DATE or CAST(X AS DATE)

For example:
SELECT DISTINCT MIN(DATE)::DATE ....

Thanx for the CAST syntax. That will be useful in the future.

However, the result still displays the time. I only want to display the date portion.
root@sl73commapd003.visa.com:9080> select distinct min(date)::DATE, max(date)::DATE, caller, component from api group by caller, component having max(date)::DATE < current_date() order by max(date);
±--------------------------------±--------------------------------±----------------------±------------------------+
| CAST(min(date) AS DATE) | CAST(max(date) AS DATE) | caller | component |
±--------------------------------±--------------------------------±----------------------±------------------------+
| 2015-09-11 00:00:00 +0000 +0000 | 2015-10-01 00:00:00 +0000 +0000 | dps-services | dfmservices |
| 2015-10-20 00:00:00 +0000 +0000 | 2015-10-28 00:00:00 +0000 +0000 | consumer-controller | vmanage-test-agent |
| 2016-02-02 00:00:00 +0000 +0000 | 2016-03-12 00:00:00 +0000 +0000 | consumer-controller | websrv_commerce |
| 2015-09-11 00:00:00 +0000 +0000 | 2016-03-14 00:00:00 +0000 +0000 | cbp-web | rsrv_CDISI_VTSTRANHIST |
| 2015-09-11 00:00:00 +0000 +0000 | 2016-03-14 00:00:00 +0000 +0000 | cbp-web | rsrv_CDISI_VTSTKNREGSTN |
| 2015-09-11 00:00:00 +0000 +0000 | 2016-04-06 00:00:00 +0000 +0000 | cbp-web | eag |
| 2016-01-27 00:00:00 +0000 +0000 | 2016-04-15 00:00:00 +0000 +0000 | dfmservices | rsrv_CDISI_MRCHSRCH_TC |
| 2016-02-04 00:00:00 +0000 +0000 | 2016-04-15 00:00:00 +0000 +0000 | ddtservices | THM-Outbound |
| 2016-04-01 00:00:00 +0000 +0000 | 2016-05-06 00:00:00 +0000 +0000 | profile-services | pips |
| 2015-09-11 00:00:00 +0000 +0000 | 2016-05-18 00:00:00 +0000 +0000 | vmcp-config-web | dcs-rest |
| 2015-09-11 00:00:00 +0000 +0000 | 2016-06-09 00:00:00 +0000 +0000 | | ptsdashboard |
| 2016-01-27 00:00:00 +0000 +0000 | 2016-06-16 00:00:00 +0000 +0000 | consumer-controller | https |
| 2016-04-01 00:00:00 +0000 +0000 | 2016-06-22 00:00:00 +0000 +0000 | profile-services | dps-services |
| 2016-07-05 00:00:00 +0000 +0000 | 2016-07-05 00:00:00 +0000 +0000 | EcipTxnHist | rsrv_mobilesrvmsg |
| 2016-03-12 00:00:00 +0000 +0000 | 2016-07-19

I want the the output display as below. How can I accomplish this?

  +---------------------------------+---------------------------------+-----------------------+-------------------------+
    |     CAST(min(date) AS DATE)     |     CAST(max(date) AS DATE)     |        caller         |        component        |
    +---------------------------------+---------------------------------+-----------------------+-------------------------+
    | 2015-09-11 | 2015-10-01 | dps-services          | dfmservices             |

Try this: SUBSTRING(X::STRING, 1, 10)
For example: SUBSTRING(MIN(date)::STRING, 1, 10)

Thanx, that did the trick?

BTW, any plans for supporting date and time variables such as Splunk variables?

That will make it more flexible to format the date or timestamp…

https://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Commontimeformatvariables#Date_and_time_variables

We’re envisioning strptime/strftime. I’m working on a PR: https://github.com/cockroachdb/cockroach/pull/9762

That works as well :slight_smile: