Forem

Wendy Calderon
Wendy Calderon

Posted on

16 7

SQL: Timestamps

The TIMESTAMP data type holds values that contain both date and time, fixed at 19 characters. The format of a TIMESTAMP is 'YYYY-MM-DD HH:MM:SS'

In DBMSs such as MySQL, DATETIME is a supported data type that, just like TIMESTAMP, is used to hold date and time values, but they're not entirely the same for the following reasons:

  • Size: DATETIME requires a few more bytes for data storing than TIMESTAMP.
  • As specified in the MySQL documentation, MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval, which does not occur for other types such as DATETIME. This is particularly convenient when working with different time zones.
  • Different supported ranges: '1000-01-01 00:00:00' to '9999-12-31 23:59:59' for DATETIME, '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC for TIMESTAMP.

Retrieving data from TIMESTAMP:
In order to fetch information from a TIMESTAMP object, we can use the extract() function, for which we specify the field from TIMESTAMP we want. Examples of this field would be:

Timestamp fields examples

Let's consider the following table to work on some examples:

Table for timestamp examples

All payments were made in november, so let's grab these bills along with the days they were paid:

Timestamp query

We add the AS statement to name the resulting column from using EXTRACT.

Timestamp query results

If our bills table was larger, including payments to these bills all throughout the year, we could implement extract() and an aggregate function (SUM) to show, for example, the total amount of money paid in bills by month, with a query similar to this one:

Another timestamp query

The larger our databases, the more ways available to retrieve info!
Have fun!

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (4)

Collapse
 
douglasfugazi profile image
Douglas Fugazi •

Super nice explanation. Thanks for sharing Wendy.

Collapse
 
wendisha profile image
Wendy Calderon •

Glad you liked it! 😊

Collapse
 
maeve70 profile image
LJ •

Thank you. This makes more sense than any documentation I've read elsewhere. :)

Collapse
 
wendisha profile image
Wendy Calderon •

Sorry for the laaaate reply! Glad you found it useful!

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay