question

cello@sprintmail.com avatar image
cello@sprintmail.com asked Erick Ramirez commented

How do I display the release_date in DS220 Exercise 5.4 in UTC format?

This is the query I have developed using the CAST function to solve this exercise. I don't see that it is possible to use the CAST function to display the release_date using UTC formats as shown on this page.

I can see displaying the UTC timestamp format could be used programmatically but I don't see how it can be in cql. Can someone provide advice?

select cast(release_date as date) as release_date, genres from videos where genres contains 'Comedy' limit 5 allow filtering;

 release_date | genres
--------------+-----------------------------------------------------------------------------------------
   1994-03-30 | {'Comedy', 'Parody', 'Satire'}
   1991-08-02 | {'Comedy', 'Comedy of manners', 'Romance Film', 'Romantic comedy'}
   2004-01-01 | {'Comedy', 'Mockumentary', 'Parody', 'Sports'}
   2002-11-01 | {'Action Film', 'Action/Adventure', 'Adventure Film', 'Comedy', 'Spy film', 'Thriller'}
   2002-12-06 | {'Comedy', 'Crime Fiction', 'Drama'}

(5 rows)
ds220
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered Erick Ramirez commented

UTC isn't a format -- it's a timezone. cqlsh displays time and date types in UTC by default.

The exercise mentions UTC just so students are not distracted with trying to display the results in their local timezone. Cheers!

2 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

cello@sprintmail.com avatar image cello@sprintmail.com commented ·

Erick,

1. Can you point me to the doc for formatting default display of time stamp in Cassandra?

2. Is it possible to do this on the Oracle virtual box instance of Cassandra installed for class exercises?

https://docs.datastax.com/en/dse/5.1/cql/cql/cql_reference/refDateTimeFormats.html

0 Likes 0 ·
Erick Ramirez avatar image Erick Ramirez ♦♦ cello@sprintmail.com commented ·

The formatting is done by the app/client you are using. You need to configure the app or client to display outputs in the format you want.

cqlsh is just another client. You're looking at the CQL date/time format instead of cqlsh formatting.

The cqlshrc file is the configuration file for the cqlsh app and it is configured with UTC by default:

[ui]
;; Used for displaying timestamps (and reading them with COPY)
time_format = %Y-%m-%d %H:%M:%S%z

;; Display timezone
timezone = Etc/UTC

To display timestamps in another timezone, you'll need to install the pytz library as documented here. Cheers!

0 Likes 0 ·