The interval is defined using plain-English terms like '10 seconds' or '5 months'. You can introduce intervals using the INTERVAL function as well: SELECT companies., In the example above, you can see that the time_to_acquisition column is an interval, not another date. JOIN tutorial.crunchbase_acquisitions_clean_date acquisitions SELECT companies.,Ĭompanies.founded_at_clean::timestamp AS time_to_acquisitionįROM tutorial.crunchbase_companies_clean_date companies Note that because the companies.founded_at_clean column is stored as a string, it must be cast as a timestamp before it can be subtracted from another timestamp. The following query uses date subtraction to determine how long it took companies to be acquired (unacquired companies and those without dates entered were filtered out).
Convert string to date sql series#
When you perform arithmetic on dates (such as subtracting one date from another), the results are often stored as the interval data type-a series of integers that represent a period of time. These are trivially simple, but it's important to keep in mind that the data type of your results will depend on exactly what you are doing to the dates. Or maybe you'd like to create a field that indicates how many days apart the values in two other date fields are. Maybe you'd like to calculate a field of dates a week after an existing field. Crazy rules for dates and timesĪssuming you've got some dates properly stored as a date or time data type, you can do some pretty powerful things. The lesson on data cleaning provides some examples for converting poorly formatted dates into proper date-formatted fields.
Note that the cleaned date field is actually stored as a string, but still sorts in chronological order anyway: SELECT , Here's an example from the same table, but with a field that has a cleaned date. Excel is notorious for producing date formats that don't play nicely with other systems, so if you're exporting Excel files to CSV and uploading them to Mode, you may run into this a lot. Mode (and most relational databases) format dates as YYYY-MM-DD, a format that makes a lot of sense because it will sort in the same order whether it's stored as a date or as a string. You might think that converting these values from string to date might solve the problem, but it's actually not quite so simple.
Because the month is listed first, the ORDER BY statement doesn't produce a chronological list: SELECT ,įROM tutorial.crunchbase_companies_clean_date For example, here's a date field stored as a string.
The problem with both of these formats is that when they are stored as strings, they don't sort in chronological order. It's an odd convention compared to the rest of the world's standards, but it's not necessarily any worse than DD-MM-YYYY. If you live in the United States, you're probably used to seeing dates formatted as MM-DD-YYYY or a similar, month-first format. This lesson uses the same data from previous lessons, which was pulled from Crunchbase on Feb. Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis.
0 kommentar(er)
