Converting year and month (yyyy-mm format) to a date?
Try this. (Here we use text=Lines
to keep the example self contained but in reality we would replace it with the file name.)
Lines <- "2009-01 12
2009-02 310
2009-03 2379
2009-04 234
2009-05 14
2009-08 1
2009-09 34
2009-10 2386"
library(zoo)
z <- read.zoo(text = Lines, FUN = as.yearmon)
plot(z)
The X axis is not so pretty with this data but if you have more data in reality it might be ok or you can use the code for a fancy X axis shown in the examples section of ?plot.zoo
.
The zoo series, z
, that is created above has a "yearmon"
time index and looks like this:
> z
Jan 2009 Feb 2009 Mar 2009 Apr 2009 May 2009 Aug 2009 Sep 2009 Oct 2009
12 310 2379 234 14 1 34 2386
"yearmon"
can be used alone as well:
> as.yearmon("2000-03")
[1] "Mar 2000"
Note:
"yearmon"
class objects sort in calendar order.This will plot the monthly points at equally spaced intervals which is likely what is wanted; however, if it were desired to plot the points at unequally spaced intervals spaced in proportion to the number of days in each month then convert the index of
z
to"Date"
class:time(z) <- as.Date(time(z))
.
how to get a date in YYYY-MM format?
Assuming that column is defined as timestamp
or timestamptz
(which it really should be) then you can use to_char()
to format the output:
select to_char(the_timestamp_column, 'yyyy-mm')
from the_table;
Convert date to YYYYMM format
SELECT LEFT(CONVERT(varchar, GetDate(),112),6)
Format JavaScript date as yyyy-mm-dd
You can do:
function formatDate(date) {
var d = new Date(date),
month = '' + (d.getMonth() + 1),
day = '' + d.getDate(),
year = d.getFullYear();
if (month.length < 2)
month = '0' + month;
if (day.length < 2)
day = '0' + day;
return [year, month, day].join('-');
}
console.log(formatDate('Sun May 11,2014'));
Convert a yyyymm format to date in R?
A date should always have a given day, therefore consider to add the day at the end of YYYYMM
(in the example below 01
the first day of the month). Convert the numeric
value to character
and the character
to a Date
:
as.Date(paste0(as.character(201603), '01'), format='%Y%m%d')
convert numeric week of year to a date (yyyy-mm-dd) in hiveql
To get accurate date you need to provide also week day along with year and week number in a year.
select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43, 7', 'yyyy, w, u'), 'yyyy-MM-dd');
Returns:
43 2020-10-18
It looks like week number in a year counted from Sundays and day number in a week is counted from Mondays because Monday is 19th:
select date_format('2020-10-18','w u'), from_unixtime(unix_timestamp('2020, 43, 1', 'yyyy, w, u'), 'yyyy-MM-dd');
returns
43 2020-10-19
If that is true, you can fix it by subtracting 60*60*24
from unix_timestamp:
select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43, 1', 'yyyy, w, u')-60*60*24, 'yyyy-MM-dd');
Returns:
43 2020-10-18
UPDATE: Surprisingly, if not providing day in a week, only year and week number, it works also counting Sunday as a week day by default but it will be not correct for other dates for example 2020-01-20, it will return the same Sunday 2020-01-18, check it yourself:
select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43', 'yyyy, w'), 'yyyy-MM-dd');
returns:
43 2020-10-18
So, if you do not have day in a week and do not need absolutely accurate date, then use
from_unixtime(unix_timestamp('2020, 43', 'yyyy, w'), 'yyyy-MM-dd');
Or like this (year and week number are selected from the table):
select from_unixtime(unix_timestamp(concat(col_year, ', ', col_week), 'yyyy, w'), 'yyyy-MM-dd') from your_table;
Related Topics
How to Append a Sequential Number for Every Element in a Data Frame
Easier Way to Use Grepl and Ifelse Across Multiple Columns
Counting Unique/Distinct Values by Group in a Data Frame
Select/Assign to Data.Table When Variable Names Are Stored in a Character Vector
In R, How to Get an Object'S Name After It Is Sent to a Function
How to Use Greek Symbols in Ggplot2
Why Does Data.Table Update Names(Dt) by Reference, Even If I Assign to Another Variable
How to Change Language Settings in R
How to Generate Permutations or Combinations of Object in R
Using Reshape from Wide to Long in R
General Suggestions For Debugging in R
Filter Rows Which Contain a Certain String
Explicitly Calling Return in a Function or Not
Use First Row Data as Column Names in R